VBA to open other workbooks based on tab names

[CreativeUsername]

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.

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]

 

[Coding4Fun]

Notice I store the sheetname as a variable and then use the variable in the call to open the file, you were close.

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

 

[CreativeUsername]

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'.

 

[CreativeUsername]

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]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.[/TD]
[/TR]
</tbody>[/TABLE]

 

[AkaTrouble]

untested but you might need to reference activesheet.name instead of ws.name

 

[tonyyy]

CreativeUsername,

Welcome to the Board.

You might consider the following...

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

 

[tonyyy]

Sorry, please add the edit in red...

Cells(destinationWB.Sheets(Sheets.Count).Cells(Rows.Count, 2).End(xlUp)[COLOR=#ff0000].Row[/COLOR] + 1, 2)

 

[CreativeUsername]

Tonyyy
You are a life saver. this works perfectly. I need to change the landing destination a bit that is all.

 

[CreativeUsername]

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?

 

[CreativeUsername]

Sorry.... the ".Row"didn't save and got dropped. Its working perfectly.