(Solved) – Copy worksheet from another workbook where the worksheet name can change depending on input

(solved)-–-copy-worksheet-from-another-workbook-where-the-worksheet-name-can-change-depending-on-input

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 Test2019-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?

Thank you in advance

Leave a Reply

Your email address will not be published. Required fields are marked *