Copy data from sheet into master worbook and save as new workbook. Repeat for all sheets

SOLVED

I used the code inserted below with changes to the path names.


Hello,

I have 3 workbooks in C:\Folder A

Data.xlsm (includes 10 worksheets named by year. e.g 2012, 2011, 2012 etc....)
Master.xlsm (includes 2 worksheets named "Criteria" and "others")
Summary.xlsm

I would like a macro placed in the Summary workbook to do the following:

1. Open the Data and Master workbooks
2. Copy range ("A2:C400") from Data Sheet("2012")
3. Paste into same range ("A2:C400") in the Master workbook, Sheet("Criteria")
4. Save as New Workbook in New Folder C:\Folder "B" with the name of the Data sheet. i.e 2012.xslm
5. Repeat for the other 9 worksheets in the Data workbook.

Result: 10 new workbooks in Folder B with the data from the different worksheets in the Data workbook.


Can you help please?

Thanks

Czap




Sub CopyDataToNewWorkbooks()
Dim dataWorkbook As Workbook
Dim masterWorkbook As Workbook
Dim summaryWorkbook As Workbook
Dim dataSheet As Worksheet
Dim masterSheet As Worksheet
Dim newWorkbook As Workbook
Dim newFolderPath As String
Dim yearSheet As Worksheet
Dim dataRange As Range
Dim targetRange As Range

' Set the paths and filenames
Dim dataPath As String
Dim masterPath As String
Dim summaryPath As String

dataPath = "C:\Folder A\Data.xlsm"
masterPath = "C:\Folder A\Master.xlsm"
summaryPath = "C:\Folder A\Summary.xlsm"

' Set the target folder path for new workbooks
newFolderPath = "C:\Folder B"

' Open workbooks
Set dataWorkbook = Workbooks.Open(dataPath)
Set masterWorkbook = Workbooks.Open(masterPath)
Set summaryWorkbook = Workbooks.Open(summaryPath)

' Loop through each year sheet in the Data workbook
For Each yearSheet In dataWorkbook.Sheets
If yearSheet.Name <> "Criteria" And yearSheet.Name <> "others" Then
' Set source and target ranges
Set dataRange = yearSheet.Range("A2:C400")
Set masterSheet = masterWorkbook.Sheets("others")
Set targetRange = masterSheet.Range("A2:C400")

' Copy data from Data workbook to Master workbook
targetRange.Value = dataRange.Value

' Create a new workbook and save with the year sheet name
Set newWorkbook = Workbooks.Add
newWorkbook.Sheets(1).Range("A1").Value = targetRange.Value
newWorkbook.SaveAs newFolderPath & yearSheet.Name & ".xlsm", FileFormat:=52 ' xlOpenXMLWorkbookMacroEnabled
newWorkbook.Close SaveChanges:=False
End If
Next yearSheet

' Close workbooks
dataWorkbook.Close SaveChanges:=False
masterWorkbook.Close SaveChanges:=True ' Save changes made in Master workbook
summaryWorkbook.Close SaveChanges:=False
End Sub