How to Make Current Open Worksheet as Active

[momer]

Hi All - I have a macro that's importing data from other Excel sheet only if the sheet name (Ex: Sheet1) is same as its referring in Macro (Sheet1), But the Sheet name is keep changing (Ex: Sheet4). Can anyone please share how to pick the name of the current tab on Source sheet (Source Excel) and use it in existing macro.

Sorry if the quesiton isn't clear enough.

Thanks in Advance,

[HaHoBe]

Hi, momer,

that should be ActiveSheet.Name or more Workbooks("YourWorkbook.xlsx").ActiveSheet.Name

Ciao,
Holger

[momer]

Macro is fetching the name of the file using the Input Box and not sure how Can I use activesheet in below Code.

Please Login or Register  to view this content.

[HaHoBe]

Hi, momer,

from what I understand you will use the same Sheetname in both workbooks:

Please Login or Register  to view this content.

If you just want to rely on the ActiveSheet in the opened workbook:

Please Login or Register  to view this content.

Ciao,
Holger

[momer]

Hi Holger - Thanks for such a prompt response. The below code is making the current active sheet i.e Sheet1 as Active and copying the data over to another sheet. But what I am looking for is a way to select a tab from Source file ( A sheet name: which user had saved at the time of closure) and use that sheet data data to copy over on existing open Sheet2.
From the above code I posted, Currently its pulling the data from file: Sales_Data and Sheetname(Data_Sheet ) but the sheetname is keep changing and here I need help to select whatever sheet was active(when it had saved).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[HaHoBe]

Hi, momer,

could you please do me a favour and explain to me what you think the line

Please Login or Register  to view this content.

is doing and maybe what should be altered where - sorry but I have lost track on your description.

Ciao,
Holger

[momer]

Apologies for not being so clear, If I am correct the above line is copying the data within the given range from active sheet to Wb1 sheet.
When I ran the macro, It is copying the sheet1 data range on Sheet2. but what I am looking for is to pull the data from different excel file (here the Source sheet name varies everytime) on Sheet2 of the open file.

Many thanks,
Omer

[HaHoBe]

Hi, momer,

you should make clear if Sheet2 is the name displayed on the worksheet tab or the codename for the sheet In your code you have both used Worksheets(2) which would mean the second worksheet in the collection of worksheets as well as Worksheets("Sheet2"). So you could replace

Please Login or Register  to view this content.

by

Please Login or Register  to view this content.

Ciao,
Holger