Hi there. I am new to Excel macro and this is my time attempt to write a macro for my new job. The objective of writing this macro is to automate the copy of selected data from one worksheet into another worksheet.
The data I need to copy is from a finance master list with hundreds to thousands of rows. After applying filter based on dates, I need to copy selected data from this master list, starting with the first row, and paste them into a table in another worksheet. This process will repeat for the next row in the master list, until the end of the filtered list. Do note that the data required from master list, Sheet1, are from one row but in different column, and need to be pasted into Sheet2 on separate row and column.
I only managed to create the first set of codes, that is to copy from Sheet1 to Sheet2, and then in Sheet2, duplicate the table to the next empty row, with two empty rows in between from the previous table. I am stuck now as I don't know how to loop the steps, in order to repeat the same process from the second row in Sheet1, and paste the new data into a new table in Sheet2. Can someone please help? Many thanks in advance.
Below are my code:
Sub CopyToJournal()
'Copy selected data from master list in Sheet1 to Journal in Sheet2
Sheets("Sheet1").Range("K4656").Copy Sheets("Sheet2").Range("D2:F2")
Sheets("Sheet1").Range("G4656").Copy Sheets("Sheet2").Range("H2")
Sheets("Sheet1").Range("J4656").Copy Sheets("Sheet2").Range("C6")
Sheets("Sheet1").Range("O4656").Copy
Sheets("Sheet2").Range("D6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Range("L4656").Copy Sheets("Sheet2").Range("G6:H6")
Sheets("Sheet1").Range("C4656").Copy Sheets("Journal").Range("C7")
Sheets("Sheet1").Range("O4656").Copy
Sheets("Sheet2").Range("D7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Range("P4656").Copy Sheets("Sheet2").Range("E7")
Sheets("Sheet1").Range("L4656").Copy Sheets("Sheet2").Range("G7:H7")
Sheets("Sheet1").Range("S4656").Copy Sheets("Sheet2").Range("D8")
'Create additional table after copy
Sheets("Sheet2").Range("B2:H8").Copy Sheets("Sheet2").Range("B11")
End Sub