Selecting Worksheet from a Range?

[HalifaxFox]

Hi,

I am fairly new to Non-Recorded Macros.

I have a Test.xls spreadsheet and am trying to create a macro that will select files and sheet names from different Ranges input in that Test.xls file. I want both the files and the specific tabs/worksheets from the files to be variable, and selected from the defined range.

Currently, I have the following code:

varWorkbook = "Variable Workbook Location" & Range("L1").Value
Workbooks.Open varWorkbook
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("Test.xls").Activate
Sheets("01").Select
Range("A1").Select
ActiveSheet.Paste


The formula works for selecting the custom workbook based on the workbook name I input in cell L1 of Test.xls.

I want to duplicate that for the tab name from the workbook, to replace the Sheets("Sheet1").Select with whatever sheet name I put in, say, cell M1 of the Test.xls workbook.


Any advice would be much appreciated!

 

[AlphaFrog]

    Dim wbVar As Workbook, wsVar As Worksheet
    Dim wsDest As Worksheet
    Dim varWorkbook As String, varWorksheet As String
        
    varWorkbook = "[COLOR="Red"]C:\MyFolder\[/COLOR]" & Range("L1").Value
    varWorksheet = Range("M1").Value
    
    Application.ScreenUpdating = False
    
    Set wsDest = ThisWorkbook.Sheets("01")
    Set wbVar = Workbooks.Open(varWorkbook)
    Set wsVar = wbVar.Sheets(varWorksheet)
    
    wsVar.UsedRange.Copy Destination:=wsDest.Range("A1")
    
    [COLOR="Green"]' Close the varWorkbook[/COLOR]
    wbVar.Close SaveChanges:=False
    
    Application.ScreenUpdating = True

 

[HalifaxFox]

Thanks!!!