Jacksmith123
New Member
- Joined
- Jun 12, 2015
- Messages
- 1
Hello Excel friends,
I'm new to this website, but not new to Excel. I require assistance with writing a macro that will copy data from multiple worksheets and paste the data to a master worksheet. Currently there are 3 worksheets named: Pivot 1, Pivot 2, and Master worksheet. The remaining worksheets are the ones I need to copy the data from. This workbook will grow with more worksheets being added to it on a bi-weekly basis.
Here are the steps in which the code needs to be written:
1) Data needs to be copied from a specific range in each worksheet. The worksheets are named by Date on a bi-weekly period basis i.e June 15, 2015, June 30, 2015 and so on.
2) For example, I may need to copy the data from Cells B8:AM200 in each worksheet. Although the starting cell will always be the same (that is B8) the number of rows and columns could vary as more worksheets get added to the workbook with data ranges being dynamic. The last row in each worksheet contains "total" information which does not need to be copied.
3) Before the data can be copied to the master worksheet, a formula needs to be inserted in cell A7 which will reference the sheet name. I'm wondering if it is possible to auto-fill this column (all cells below A7) to data that exists in the adjacent column. So if there are 200 rows that contain data in Column B, Column A will also auto-fill down to 200 rows.
4) Once this is done, I would like to copy the data to the master worksheet for each worksheet, keeping in mind that new worksheets will be added on a continuous basis. So if a new worksheet gets added it can easily be copied to the master worksheet without duplicating or writing over the information in the master worksheet.
Here is the code I have so far, but it is obviously not working:
I'm new to this website, but not new to Excel. I require assistance with writing a macro that will copy data from multiple worksheets and paste the data to a master worksheet. Currently there are 3 worksheets named: Pivot 1, Pivot 2, and Master worksheet. The remaining worksheets are the ones I need to copy the data from. This workbook will grow with more worksheets being added to it on a bi-weekly basis.
Here are the steps in which the code needs to be written:
1) Data needs to be copied from a specific range in each worksheet. The worksheets are named by Date on a bi-weekly period basis i.e June 15, 2015, June 30, 2015 and so on.
2) For example, I may need to copy the data from Cells B8:AM200 in each worksheet. Although the starting cell will always be the same (that is B8) the number of rows and columns could vary as more worksheets get added to the workbook with data ranges being dynamic. The last row in each worksheet contains "total" information which does not need to be copied.
3) Before the data can be copied to the master worksheet, a formula needs to be inserted in cell A7 which will reference the sheet name. I'm wondering if it is possible to auto-fill this column (all cells below A7) to data that exists in the adjacent column. So if there are 200 rows that contain data in Column B, Column A will also auto-fill down to 200 rows.
4) Once this is done, I would like to copy the data to the master worksheet for each worksheet, keeping in mind that new worksheets will be added on a continuous basis. So if a new worksheet gets added it can easily be copied to the master worksheet without duplicating or writing over the information in the master worksheet.
Here is the code I have so far, but it is obviously not working:
Code:
Sub Copytomaster()
Dim ws As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Master" Or ws.Name <> "Pivot 1" Or ws.Name <> "Pivot 2" Then
LR1 = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
LR2 = ws.Range("C" & Rows.Count).End(xlUp).Row
Range("A6").Select
ActiveCell.FormulaR1C1 = "Payperiod"
Range("A7").Select
ActiveCell.FormulaR1C1 = ActiveSheet.Name
Range("$A7:$A" & LR2).formula = ActiveSheet.Name
Selection.Copy
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ws.Range("A7:AM" & LR2).Copy Destination:=Sheets("Master").Range("A" & LR1)
Sheets("Master").Select
Range("A1").Select
End If
Next ws
Application.ScreenUpdating = True
End Sub