Paste into variable named worksheet

[kbd230]

Hi,

In my macro I need to copy paste a data range from one workbook to another. The data source workbook is always the same name but the destination workbook changes periodically and has no naming convention.
The destination workbook is also not the workbook in which the VBA code runs, so I can't use 'ThisWorkbook'. The destination workbook does however have a sheet which is always called the same, but I can't find out how (if possible) activating a sheet which is within a variable-named workbook.

Here's my code:

Sub Macro17()
'
' Macro17 Macro
'
Windows("datasource.csv").Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("destination.xlsx").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

 

[offthelip]

This code loops through all workbooks that are open and searches each one to find the worksheet name "CPU" and then activate and selelcts that worksheet to allow you to paste to that worksheet.
Obviously changethe name to the workhseet name you are looking for or even set it dynamically:

Sub findsheet()
nametofind = "CPU"
Dim wb As Workbook
Dim ws As Worksheet
exitloop = False
For Each wb In Application.Workbooks
 wb.Activate
 For Each ws In Worksheets
    If ws.Name = nametofind Then
     exitloop = True
     ws.Select
     ''' you can put your paste code here!!
         
     Exit For
    End If
Next ws
If exitloop Then Exit For
Next wb

End Sub

 

[kbd230]

This code loops through all workbooks that are open and searches each one to find the worksheet name "CPU" and then activate and selelcts that worksheet to allow you to paste to that worksheet.
Obviously changethe name to the workhseet name you are looking for or even set it dynamically:

Sub findsheet()
nametofind = "CPU"
Dim wb As Workbook
Dim ws As Worksheet
exitloop = False
For Each wb In Application.Workbooks
 wb.Activate
 For Each ws In Worksheets
    If ws.Name = nametofind Then
     exitloop = True
     ws.Select
     ''' you can put your paste code here!!
        
     Exit For
    End If
Next ws
If exitloop Then Exit For
Next wb

End Sub

Thank you, also for the explanation so I learn by doing, works like a charm!