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