Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Batch file to copy sheet

    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!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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 drive\path 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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,413
    Thanks
    33
    Thanked 195 Times in 175 Posts
    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

    Time prevents everything happening all at once...

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks for saving me a lot of drudgery!
    greatly appreciated

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Wassim,

    Somehow, I don't think linking 185 workbooks is a very good idea.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •