VBA to open other workbooks based on tab names

CreativeUsername

Board Regular
Joined
Mar 11, 2017
Messages
52
I've been scouring the web and not finding what I need. Also "Excel 2013 Power Programming with VBA" hasn't been able to show me what I need. The code below Is intended to copy data from a range on all tabs except the first one "Skip Me" and paste it into other workbooks. The tabs in "master" correspond to workbooks in the same folder as "Master". So Tabs are : "Alex, Bill, and Sue". Workbooks are "Processor Alex", Processor Bill, Processor Sue. . .Etc. I'm not married to those names in particular. Tabs could include Processor or Processor could be dropped from the file names.

I'm struggling with the part where it finds and opens another workbook. I think i'm really close. I works perfectly if I give it the exact file name but then there is no need for a loop and this is just a test sample. The real project has 12 workbooks and Processors may change so hard coding destinations is cumbersome. The loop works with in the same workbook. So how to make it work, I'm Stumped.

Code:
Sub UpdatebyLoop()


Dim ws As Worksheet
Dim SourceWB As Workbook


Set SourceWB = ThisWorkbook
Application.ScreenUpdating = False


For Each ws In Worksheets
    If ws.Name <> "Skip Me" Then
                  'Debug.Print ws.Name
        ws.Activate
        ws.Select
        Range("A2:M10").Select
        Selection.Copy
        


Workbooks.Open ("C:\Users\Scott\Desktop\VBA Proj\Processor [U][B]" & ws.Name & ".xlsx"[/B][/U]) [COLOR=#ff0000]<----This part gives a "1004" error[/COLOR]
    
    Worksheets(Worksheets.Count).Select 'Selects last worksheet page
    
'Selects destination looking for first blank cell in "B"
        'Set ws = ActiveSheet
        For Each Cell In ws.Columns(2).Cells
            If IsEmpty(Cell) = True Then Cell.Select: Exit For
            Next Cell
                              
        ActiveSheet.Paste 'Pastes selection
        
    Application.CutCopyMode = False 'Clears Clipboard for next copy action
  
    End If


Next ws


End Sub [code]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Notice I store the sheetname as a variable and then use the variable in the call to open the file, you were close.

Code:
Sub UpdatebyLoop()



Dim ws As Worksheet
Dim SourceWB As Workbook




Set SourceWB = ThisWorkbook
Application.ScreenUpdating = False




For Each ws In Worksheets
If ws.Name <> "Skip Me" Then
'Debug.Print ws.Name
ws.Activate
'ws.Select - redundant
Range("A2:M10").Select
Selection.Copy


Sheet = ws.Name


Workbooks.Open ("C:\Users\UserName\Desktop\" & Sheet & ".xlsx") '<----This part gives a "1004" error


Worksheets(Worksheets.Count).Select 'Selects last worksheet page


'Selects destination looking for first blank cell in "B"
'Set ws = ActiveSheet
For Each Cell In ws.Columns(2).Cells
If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell


ActiveSheet.Paste 'Pastes selection


Application.CutCopyMode = False 'Clears Clipboard for next copy action


End If




Next ws




End Sub
 
Upvote 0
I still get the same error in the:
Workbooks.Open ("C:\Users\UserName\Desktop" & Sheet & ".xlsx") '<----This part gives a "1004" error

What am I missing? Oh the actual file is "processor" 'sheetname'.
 
Upvote 0
C:\Users\Scott\Desktop\VBA Proj\[Sue.xlsx]Sheet1

is my file path if I take the "Processor part off. Sould it be my syntax? I copy and pasted it. Above is gotten by "=cell("filenam,A1") in Sue.

<tbody>
</tbody>
 
Upvote 0
CreativeUsername,

Welcome to the Board.

You might consider the following...

Code:
Sub UpdatebyLoop()
Application.ScreenUpdating = False
Dim SourceWB As Workbook, destinationWB As Workbook
Dim ws As Worksheet

Set SourceWB = ThisWorkbook
On Error GoTo errHandler
For Each ws In SourceWB.Worksheets
    If ws.Name <> "Skip Me" Then
        Set destinationWB = Workbooks.Open(SourceWB.Path & "\" & ws.Name & ".xlsx")
        ws.Range("A2:M10").Copy Destination:=destinationWB.Sheets(Sheets.Count). _
            Cells(destinationWB.Sheets(Sheets.Count).Cells(Rows.Count, 2).End(xlUp) + 1, 2)
        destinationWB.Close savechanges:=True
    End If
Next ws
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With
Exit Sub

errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & _
    "Sorry, it seems the worksheet name - " & ws.Name & " - does not match a workbook name."
    Resume Next
End Sub

The code adds another variable - "destinationWB" - to make it easier to reference the workbook that matches each sheet name in "master." It also eliminates the "Activate" and "Select" methods as they're really not needed and only tend to slow the execution time.

An "errHandler" routine was added and will trigger if the code can't find a match between the sheet name in "master" and a workbook in the same folder.

Cheers,

tonyyy
 
Last edited:
Upvote 0
Sorry, please add the edit in red...
Code:
Cells(destinationWB.Sheets(Sheets.Count).Cells(Rows.Count, 2).End(xlUp)[COLOR=#ff0000].Row[/COLOR] + 1, 2)
 
Upvote 0
Wait it worked once but now I get an error message about the file name not matching. It does. And it seems to stop all the others from updating also. How did it work once then stop?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top