Batch file to copy sheet

Topic

New Reply

I would like to have a batch file that would copy a sheet from a spreadsheet to each of 185 spreadsheets.
The sheet to be copied would be [count.xlsx]Count!
The spreadsheets are ARM1.xlsx through ARM185.xlsx.

suggestions?
thanks!

Reply | Quote

Replies

J.E.

This VBA placed in the count.xlsx workbook and renamed to count.xlsm should do the trick.

Code:

Option Explicit

Sub MassCopySheet()

   Dim wkbkSource     As Workbook
   Dim wsSource       As Worksheet
   Dim wkbkDest       As Workbook
   Dim iWkBKCntr      As Integer

   Application.ScreenUpdating = False

   Set wkbkSource = ActiveWorkbook
   
   Set wsSource = wkbkSource.Sheets("Count")  'Source Sheet to copy

   For iWkBKCntr = 1 To 185

      On Error Resume Next
      Set wkbkDest = Workbooks.Open("ARM" & Format(iWkBKCntr) & ".xlsx")
      On Error GoTo 0
      
      If Not wkbkDest Is Nothing Then
        wsSource.Copy After:=wkbkDest.Sheets(1)
        Application.DisplayAlerts = False
        With wkbkDest
            .Save
            .Close
        End With
      End If  'wkbkDest  Nothing
      
   Next iWkBKCntr
   
End Sub

Notes:
1. The code compiles correctly.
2. The code runs correctly to end. Of course I don’t have any of the ARM workbooks so all I’ve really tested is the error trap for missing files in the series, which works.
3. You need to add a drivepath to the destination workbook name if it is not in the same directory as the count.xlsx file.
4. If you don’t want the copied sheet to follow Sheet 1 then you need to change the AFTER clause of the Copy command with the appropriate number or with the Sheet Name in quotes.
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

thanks for saving me a lot of drudgery!
greatly appreciated

Reply | Quote

Hi AllPardon me but why was a solution made of linking the worksheet cells in all these workbooks passed?I thought of this as I was reading the post.Any limitations on linking?Wassim

Reply | Quote

An ordinary BATch file only knows about files, and could not access a workbook within a spreadsheet.
So RetiredGeek’s VBA method is the way to go.

BATcher

Plethora means a lot to me.

Reply | Quote

Wassim,

Somehow, I don’t think linking 185 workbooks is a very good idea. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote