Copy data from another workbook using cell value as parameter for sheet?

[TheWennerWoman]

Hello,

I have the following

Dim wsCopy As Worksheet
Dim wsDest As Worksheet


Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")

In Reports.xlsm (which is where this code lives) I'd like to have a cell (A1 on a sheet named "Control") where the user types the sheet name we want to copy from in New Data - so say they type July then instead of looking in Sheet "Export 2", my macro would look in July.

Many thanks.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Control" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. You would then have to place the rest of your code where indicated in red. Close the code window to return to your sheet. Enter a value in A1 in the "Control" sheet and press the RETURN key. The macro will run automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "A1" Then Exit Sub
    Application.ScreenUpdating = False
    Dim wsCopy As Worksheet, wsDest As Worksheet
    Set wsCopy = Workbooks("New Data.xlsx").Sheets(Target.Value)
    Set wsDest = ThisWorkbook.Sheets("All Data")
    'Your code here
    Application.ScreenUpdating = True
End Sub

Another alternative would be this macro in a regular module which you would have to run manually:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim wsCopy As Worksheet, wsDest As Worksheet
    Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
    Set wsCopy = Workbooks("New Data.xlsx").Sheets(wsDest.Sheets("Control").Range("A1").Value)
    'Your code here
    Application.ScreenUpdating = True
End Sub

 

[TheWennerWoman]

Thank you for that but it's erroring at

Set wsCopy = Workbooks("New Data.xlsx").Sheets(wsDest.Sheets("Control").Range("A1").Value)

"Method or data member not found"

 

[mumps]

Oops. Try:

Set wsCopy = Workbooks("New Data.xlsx").Sheets(Workbooks("Reports.xlsm").Sheets("Control").Range("A1").Value)

 

[TheWennerWoman]

You aced it. Many thanks, much appreciated.

 

[mumps]

You are very welcome.