I was asked during a webinar recently how you send multiple worksheets to a new workbook in a batch. I was pretty sure this information would be on my site but a quick search of my site did not reveal any joy.
I put together a simple file which sends an output sheet and two source data sheets to a directory saves it then starts the process again after changing a unique identifier. The process is a little more in-depth than sending just one sheet but there is not a great deal more code involved.
In sending the data to a fresh workbook I must use the sheet name as using the sheet code name in this instance is a bit more difficult as I am working with two workbooks.
Sub BatchMultiSheet()
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
For i = 3 To 12 'Static range.
[c4] = Sheet2.Range("A" & i)
Sheets(Array("101", "Data", "FTE")).Copy 'The Sheet Names
Sheets("101").[a1:M100] = Sheets("101").[a1:M100].Value
ActiveWorkbook.SaveAs Sheets("101").[a2] & Sheets("101").[a1] & ".xlsx"
ActiveWorkbook.Close False
Next i
Application.ScreenUpdating = True
End Sub
I will attach a file to show wokings shortly as per usual.
Smallman
