Copy worksheet from another workbook where the worksheet name can change depending on input

1
votes

For work we have 1 excel workbook with many different sheets in, my role is to take the info from one of these worksheets at a time and put it into another workbook to work from. Pretty simple really, i'm currently learning VBA and am trying to automate this process to start.

This is what i have so far. It starts from a button press on the "Macro Test Blank Sheet For new Data.xlsm" workbook. 

Workbooks.Open "C:\Macro Test\2019-2020 Reflecto Record.xlsm"

 Workbooks("2019-2020 Reflecto Record.xlsm").Worksheets(1).Range("A1:BA10000").Copy _
    Workbooks("Macro Test Blank Sheet For new Data.xlsm").Worksheets("Data").Range("A1")

ActiveWorkbook.Close

When finished the data has been copied and the "2019-2020 Reflecto Record.xlsm" workbook has been closed.

At the moment it is using .Worksheet(1) to use the first page in the Workbook to copy from. Is it possible for this to pick a worksheet by name where the name is entered into a cell next to the button to start the macro?

2
excelvba
Rather than using a cell next to a button, you could also use an inputbox, or pick from a drop down list. – Plutian

2 Answers

0
votes

If you specify the name in cell A1 of a worksheet called "Sheet1" (just change the name to the worksheet where you're working)

Use this code:

Dim sourceSheetName as String

sourceSheetName = Thisworkbook.Worksheets("Sheet1").Range("A1").Value

Workbooks.Open "C:\Macro Test\2019-2020 Reflecto Record.xlsm"

Workbooks("2019-2020 Reflecto Record.xlsm").Worksheets(sourceSheetName).Range("A1:BA10000").Copy _
        Workbooks("Macro Test Blank Sheet For new Data.xlsm").Worksheets("Data").Range("A1")

ActiveWorkbook.Close

I also suggest that you look into Power Query to automate this kind of process

0
votes

This should get you started, two suggested ways of generating the name:

Sub sheetopener()
Dim MySh As String 'Dim MySh as a variable to hold the name of your sheet.

MySh = InputBox("Which sheet?", "Sheet picker") 'Set the name of your sheet with a popup box.
'Mysh = Thisworkbook.Sheet1.Range("A2").value 'Alternative set the name of your sheet in reference to a cell.

Workbooks("Name here").Sheets(MySh).Activate 'Activate the sheet with the just generated name.

End Sub