Daily Task Sheet

Malissa1578 · #1 03-14-2013, 12:14 PM

Ok I am kind of a beginner to excel in the aspect of auto-populating/ moving information from one sheet to another automatically and complex formulas and functions, so any help would be greatly appreciated. I want to move information based on completion dates in column D on one sheet to another sheet, but I want to auto-populate rows of information based on this completion date, but not all information. I do not want information in column one in the first sheet to transfer to second sheet. So based on my completion date in sheet one, column D, Row 5, I want information from column B (sheet 1) to correlate to sheet 2, column B, starting in row 3, the assigned by in sheet 1 to relate to assigned by in sheet 2, the notes to do the same thing, and so on and so forth. Once the information is moved to the second sheet due to completion date, I want the information in the first sheet to delete itself. I know you can create a button for this, but am so unsure of where to start. Can anyone explain or help me in any way to get this done? I have been looking on the internet, but nothing seems to really explain or give me what I am looking for exactly. I have attached what I am working on so that you can see what I am trying to do. Its very self explanatory.

I have started a macro and I need it to reference the next available row in the Completed tasks sheet. Can you help me with this? And also I need the macro to work for all rows and not just specified rows and cells. I have been so out of touch with Excel and I just got back into the work force, so this is all trial and error here. I had a basic Formula for the next available row, but I did it wrong, so any help would be appreciated. This is the macro I developed and it only works for one row, for that row it works great, but I need to work for all of them

... And Also once the macro is working appropriately how would I make it a button?

Code:

Sub Macro1() 
     '
     ' Macro1 Macro
     '
     
     '
    Sheets("Task Sheet").Select 
    Range("E5").Select 
    Selection.Copy 
    Sheets("Completed Tasks").Select 
    Range("A8").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .Color = 49407 
        .TintAndShade = 0 
        .PatternTintAndShade = 0 
    End With 
    With Selection.Font 
        .ColorIndex = xlAutomatic 
        .TintAndShade = 0 
    End With 
    Sheets("Task Sheet").Select 
    Range("B5:D5").Select 
    Selection.Copy 
    Sheets("Completed Tasks").Select 
    Range("B8").Select 
    ActiveSheet.Paste 
    Range("A8:D8").Select 
    Application.CutCopyMode = False 
    With Selection.Font 
        .Name = "Calibri" 
        .FontStyle = "Regular" 
        .Strikethrough = True 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .TintAndShade = 0 
        .ThemeFont = xlThemeFontMinor 
    End With 
    Sheets("Task Sheet").Select 
    Range("A5:E5").Select 
    Range("E5").Activate 
    Selection.ClearContents 
    Sheets("Completed Tasks").Select 
End Sub

Malissa1578 · #2 03-14-2013, 04:24 PM

Ok so I was messing around with the macro and cleaned it up a little bit. What I need to know is if Ican make this more generic to work with every row and not just the specified row and is there a generic code I can put in there to select the next available row to paste into on my completed tasks sheet?

here is the cleaned up macro....

Code:

Sub TaskCompleted() 
     '
     ' TaskCompleted Macro
     '
     '
    Sheets("Task Sheet").Select 
    Range("E5").Select 
    Selection.Copy 
    Sheets("Completed Tasks").Select 
    Range("A8").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .Color = 49407 
        .TintAndShade = 0 
        .PatternTintAndShade = 0 
    End With 
    Sheets("Task Sheet").Select 
    Range("B5:D5").Select 
    Selection.Copy 
    Sheets("Completed Tasks").Select 
    Range("B8:D8").Select 
    ActiveSheet.Paste 
    Range("A8:D8").Select 
    Application.CutCopyMode = False 
    With Selection.Font 
        .Name = "Calibri" 
        .FontStyle = "Regular" 
        .Strikethrough = True 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .TintAndShade = 0 
        .ThemeFont = xlThemeFontMinor 
    End With 
    Sheets("Task Sheet").Select 
    Range("A5:E5").Select 
    Selection.ClearContents 
End Sub

macropod · #3 03-19-2013, 05:23 PM

Hi Malissa,

I believe the following is what you're after:

Code:

Sub TasksCompleted()
Application.ScreenUpdating = False
Dim WS1 As Worksheet, WS2 As Worksheet
Dim lRow1 As Long, lRow2 As Long, i As Long
Set WS1 = Sheets("Task Sheet")
Set WS2 = Sheets("Completed Tasks")
lRow1 = WS1.Range("E" & WS1.Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
lRow2 = WS2.Range("A" & WS2.Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
With WS1
  For i = 2 To lRow1
    If .Cells(i, 5).Value <> "" Then
      lRow2 = lRow2 + 1
      With WS2
        .Range("A" & lRow2).Value = WS1.Range("E" & i).Value
        .Range("B" & lRow2 & ":D" & lRow2).Value = WS1.Range("B" & i & ":D" & i).Value
      End With
      WS1.Range("A" & i & ":E" & i).ClearContents
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.

Malissa1578 · #4 03-21-2013, 09:35 AM

Quote:

Originally Posted by macropod

Hi Malissa,

I believe the following is what you're after:

Code:

Sub TasksCompleted()
Application.ScreenUpdating = False
Dim WS1 As Worksheet, WS2 As Worksheet
Dim lRow1 As Long, lRow2 As Long, i As Long
Set WS1 = Sheets("Task Sheet")
Set WS2 = Sheets("Completed Tasks")
lRow1 = WS1.Range("E" & WS1.Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
lRow2 = WS2.Range("A" & WS2.Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
With WS1
  For i = 2 To lRow1
    If .Cells(i, 5).Value <> "" Then
      lRow2 = lRow2 + 1
      With WS2
        .Range("A" & lRow2).Value = WS1.Range("E" & i).Value
        .Range("B" & lRow2 & ":D" & lRow2).Value = WS1.Range("B" & i & ":D" & i).Value
      End With
      WS1.Range("A" & i & ":E" & i).ClearContents
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.

Thank You

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Two resource sheet for one task	zapco76	Project	1	11-20-2012 05:43 AM
sheet 2 data highlight in sheet 1	gsrikanth	Excel	1	04-21-2012 06:25 PM
Construct a summary sheet by summing up from one or more than one sheet.	PRADEEPB270	Excel	1	11-04-2011 03:46 AM
copy cell from sheet 2 to sheet 3 macro	slipperyjim	Excel Programming	1	02-18-2010 01:31 AM
daily calender printing	ccronin4	Outlook	1	01-10-2007 12:47 PM