For each Loop

[VBA_Amateur]

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

 

[mrmmickle1]

Try this:

[COLOR=#333333]For Each Worksheet In Workbook.Worksheets[/COLOR]

In order to switch back and forth from workbooks you can use code like this:

Windows(Template).Activate

 

[VBA_Amateur]

Thank you for this. The code does recognise the For each but gives me an error on the next few lines for the following code.
Do I replace all the 'ws' with 'worksheets'? What do I do about referencing the worksheets to find the last row or assigning a value to the cell as below.

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

 

[mrmmickle1]

Define your last row like this:

LRow = Range("A" & Rows.Count).End(xlUp).Row

Be careful with Worksheet vs. Worksheets... They are different.

Worksheet is one worksheet. Worksheets is the entire collection of worksheets....(all of them)