Please help - Moving information from one sheet to another in the same workbook

**Malissa1578** · 03-12-2013, 06:17 PM

Ok I am kind of a beginner to excel in the aspect of auto-populating/ moving information from one sheet to another automatically. 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.Daily Task Sheet.xlsx

**mehmetcik** · 03-12-2013, 06:46 PM

Sorry not very self explanatory.

the easiest way to to this is to record a macro.

Assuming you have a developer tab on excel, press that then click on record macro. [ remember the macro name ]

Go through the steps that you want to take,
Move to sheet 2, then to sheet one so that excel know where to start
Select & Copy the data you want copied across
Select Sheet Two

Select the cell you want it pasted to and paste.

Clear any cells you want cleared

Type in any formulas that you want typed.

Then

press the developer tab and press Stop macro.

on the developer tab press insert

and click on the icon on the top left of the little window

click and drag anywhere on your spreadsheet will create a button

Right click on that and press assign macro to link the button to your macro,

Now if you want the macro tidied up or improved. come back to me.

**Malissa1578** · 03-14-2013, 07:25 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....

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

Moderator's Note: Welcome to the forum. Codes should be enclosed with code tags. Select the codes then hit the "#" symbol. Thank you for joining the community.