Managing variable Workbook name VBA

[ExcelFind]

Hello,

I am looking for a VBA code that reads the active workbook name, in order to use it later as reference in order to activate/open the same workbook in question.

Background: The VBA code is activated in "workbook1.xlsm" via a script button and exports data from SAP into a temp. Excel spreadsheet, this data is then copied to "Sheet1" in "workbook1.xlsm".

Issue: When changing the workbook name of "workbook1.xlsm", for example to "workbook2.xlsm", I have to update the workbook reference name in VBA code as well in order to prevent an error.

Questions:

1. Can you help me out with a code that is reading the active workbook name, in order to use it later when the data is copied from the temp. Excel workbook?
2. Can you help me out with a code that is (first of all) checking if "Sheet1" is present in the workbook?
   

From this point in the code below, the temporary Excel spreadsheet has been created and data is copied in cell A2 of Sheet1 in workbook1.xlsm

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("workbook1.xlsm").Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste

 

[Joe4]

I would recommend using workbook objects.
Here is a simple example showing you how to assign the active workbook to a workbook object, and then showing you how you can get its name:

    Dim wb As Workbook
    Set wb = ActiveWorkbook
    MsgBox wb.Name

And once you set it equal to a workbook variable, you should be able to easily activate it like this:

wb.Activate

To check to see if a particular sheet name exists, see this code here: How To Check If A Worksheet Exists Using VBA | How To Excel

 

[ExcelFind]

Hello Joe,

Thanks for the quick reply, I tried but the VBA runs into error. Can you help me out with the code?

I added this code in the first part of the VBA script:

Dim wb As Workbook
Set wb = ActiveWorkbook

How should I now write the code for activating/calling the workbook using the workbook object, the code below is not working...

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(wb).Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste

 

[Joe4]

Hello Joe,

Thanks for the quick reply, I tried but the VBA runs into error. Can you help me out with the code?

I added this code in the first part of the VBA script:



How should I now write the code for activating/calling the workbook using the workbook object, the code below is not working...

You need to look a little closer at what I posted, specificially the second code block.
You just use:

wb.Activate

 

[ExcelFind]

Sorry, I was to fast with this and didn't noticed the second line! It is working. Many thanks!

 

[Joe4]

You are welcome.
Glad I was able to help!