Start a Macro in an active workbook that will open a different specific workbook and copy its specific sheet to the originally active workbook?

[excelos]

hello!

Start a Macro in an active workbook that will open a different specific workbook and copy its specific sheet to the originally active workbook?
and then close the different specific workbook?

 

[JLGWhiz]

Sub r()
Dim wb As Workbook
Set wb = Workbooks.Open(Workbooks("SpecificWorkbook.xlsx")
wb.Sheets("Specific").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
wb.Close
End Sub

This code will need modification to substitute workbook and sheet names. It also might require adding in a path to open the second workbook if that workbook is in a different directory than the host workbook.

 

[excelos]

Thanks!


Can you explain a bit please:
I assume wb is a variable.
Which of the rest words like Workbook, Workbooks, ThisWorkbook are builtin variables and not user-defined variables?
Can you go through each word please to explain?


For example:
Dim wb as Workbook : I understand that you set a variable named wb to be Workbook, so is Workbook a built in name for the type/class of variable for workbooks?
Then you set wb to open SpecificWorkbook but what does the Workbooks word mentioned twice mean?
Then you copy the Specific sheet from the workbook wb, into ThisWorkbook. What is the ThisWorkbook? is it builtin variable for the active workbook?

This will help me very much,
Thanks!

 

[Norie]

How many workbooks will be open when this code is executed?

When you say 'active' workbook do you mean the workbook the code is in or another workbook?

Where would the filename and path for the 'other specific' workbook come from?

Which worksheet would you want to copy from that workbook?

 

[excelos]

There will be only one workbook opened, but apparently the macro will have to open the TEMPLATEWORKBOOK to copy the TEMPLATEWORKSHEET to the ACTIVEWORKBOOK

The ACTIVEWORKBOOK is any workbook that I may open and when I click the macro, the TEMPLATEWORKSHEET should be copied to that.

I suppose by 'other specific' you mean the TEMPLATEWORKBOOK? It will be saved in the standard templates path and the filename will be TEMPLATEWORKBOOK.

I want to copy a standard named worksheet from the TEMPLATEWORKBOOK, named TEMPLATEWORKSHEET.

But please answer my questions, I don't just need the code, I need to understand what I am asking in my previous post.

 

[Norie]

Little confused now.

That sounds like you want to open two other workbooks, the TEMPLATEWORKBOOK and another workbook.

You then want to copy a sheet, TEMPLATEWORKSHEET to the other workbook that was opened.

Is that correct or am I missing something?

 

[excelos]

Little confused now.

That sounds like you want to open two other workbooks, the TEMPLATEWORKBOOK and another workbook.

You then want to copy a sheet, TEMPLATEWORKSHEET to the other workbook that was opened.

Is that correct or am I missing something?

Exactly

 

[JLGWhiz]

Thanks!


Can you explain a bit please:
I assume wb is a variable.
Which of the rest words like Workbook, Workbooks, ThisWorkbook are builtin variables and not user-defined variables?
Can you go through each word please to explain?


For example:
Dim wb as Workbook : I understand that you set a variable named wb to be Workbook, so is Workbook a built in name for the type/class of variable for workbooks?
Then you set wb to open SpecificWorkbook but what does the Workbooks word mentioned twice mean?
Then you copy the Specific sheet from the workbook wb, into ThisWorkbook. What is the ThisWorkbook? is it builtin variable for the active workbook?

This will help me very much,
Thanks!

Sub r()
Dim wb As Workbook [COLOR=#008000]'Declare object [/COLOR][COLOR=#008000]variable[/COLOR]
Set wb = Workbooks.Open(Workbooks("SpecificWorkbook.xlsx") [COLOR=#008000]'Initialize object variable[/COLOR]
wb.Sheets("Specific").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) [COLOR=#008000]'Copy sheet from opened wb to active workbook.[/COLOR]
wb.Close [COLOR=#008000]'Close opened workbook.  Add 'False' to statement to prevent alert message.[/COLOR]
End Sub

The 'wb' user defined variable equates to 'Workbooks("SpecificWorkbook.xlsx)' wherever it is used in the code.
'ThisWorkbook' refers to the workbook hosting the code and is an Excel application constant.
'Workbook' is an Excel keyword for an Excel objeect and is part of the 'Workbooks' collection.
There are numerous tutorials on the web which explain the basics of VBA. You can locate them by typing 'Free VBA Tutorial' in the web search box and press enter. Several options should show as a result.