VBA_Amateur
New Member
- Joined
- Jun 13, 2015
- Messages
- 12
I have a template which I would like to copy data to from each sheet in workbook A_Dist.xlsx.
I would like the data in each sheet of A_Dist.xlsx to be copied to the Template.xlsx, save the file and then move on to the next sheet in A_Dist.xlsx and do the same action.
The number of sheets in A_Dist.xlsx will change each time and so I would like this to be dynamic.
Here is the code I have created but I get an error highlighting the For Each syntax in yellow.
Sub CreateReport()
Dim Template As Workbook
Dim A_Dist As Workbook
Dim ws As Worksheets
Dim EndRow As Integer
Dim Site_Name As String
Set Template = Workbooks.Open("[Path]\Template.xlsx")
Set A_Dist = Workbooks.Open("("[Path]\A_Dist.xlsx")
A_Dist.Activate
For Each ws In A_Dist.Worksheets
EndRow = A_Dist.ws.Cells(Rows.Count, 1).End(xlUp).Row
Site_Name = A_Dist.ws.Range("I2").Value
A_Dist.ws.Range(Cells(2, 1), Cells(EndRow, 8)).Select
Selection.Copy
Template.Activate
Sheets("Report").Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Template.Sheets("Report").Range(Cells(6, 1), Cells(EndCopyData, 8)).Select
Application.CutCopyMode = False
Template.SaveAs ("[Path]\" & Site_Name)
'Clears the data in the template for the data for the next sheet in A_Dist
Template.Sheets("Report").Range("A6:H5000").ClearContents
Next ws
End Sub
I would like the data in each sheet of A_Dist.xlsx to be copied to the Template.xlsx, save the file and then move on to the next sheet in A_Dist.xlsx and do the same action.
The number of sheets in A_Dist.xlsx will change each time and so I would like this to be dynamic.
Here is the code I have created but I get an error highlighting the For Each syntax in yellow.
Sub CreateReport()
Dim Template As Workbook
Dim A_Dist As Workbook
Dim ws As Worksheets
Dim EndRow As Integer
Dim Site_Name As String
Set Template = Workbooks.Open("[Path]\Template.xlsx")
Set A_Dist = Workbooks.Open("("[Path]\A_Dist.xlsx")
A_Dist.Activate
For Each ws In A_Dist.Worksheets
EndRow = A_Dist.ws.Cells(Rows.Count, 1).End(xlUp).Row
Site_Name = A_Dist.ws.Range("I2").Value
A_Dist.ws.Range(Cells(2, 1), Cells(EndRow, 8)).Select
Selection.Copy
Template.Activate
Sheets("Report").Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Template.Sheets("Report").Range(Cells(6, 1), Cells(EndCopyData, 8)).Select
Application.CutCopyMode = False
Template.SaveAs ("[Path]\" & Site_Name)
'Clears the data in the template for the data for the next sheet in A_Dist
Template.Sheets("Report").Range("A6:H5000").ClearContents
Next ws
End Sub