Go to a Worksheet assigned in a Cell in another Workbook using VBA

[AyuHI]

Hi, I've been trying to solve a VBA problem but without success so far. I need to execute a great number of copy&paste commands from one workbook (let's call it MainDatabase) to another Workbook (called i.e. Unit 1) in several sheets for each month (thus, 12 in total). The MainDatabase has many worksheets from which I want to select a particular sheet (named "Unit 1"), copy data from that sheet and paste in several sheets of the "Unit 1" workbook. I'm basically separating data from a bigger database. I have to repeat this process for several workbooks (Unit 2, Unit 3, Unit 4, and so on) (always doing the same operations, but extracting from different sheets of MainDatabase, each called Unit 2, Unit 3, etc).

I'm new to VBA, and so doing research, I've succeed in creating a code which copies&pastes the way I need, provided I input inside the code the name of the specific sheet I want to be opened in MainDatabase (I have to change Unit 1 to the Unit Name I need every time inside the code), and also I always have to copy the code to every new "Unit #" Workbook to execute.

I would like to create a 'Master' Workbook (separated from MainDatabase and Unit # Workbooks) containing the code, and in a particular cell, say "B5", write the name of the Worksheet the code must go to in Main Database. Say, write "Unit 7" in cell B5 and run the code, and thus it will go to Unit 7 worksheet in MainDatabase and paste to Unit 7 Workbook.

Is this possible?


Here's what I have so far (this Copy&Paste routine repeats for each month inside code, but the main repeatable unit is as follows):

Sub MacroCopyInformation()

ThisWorkbook.Activate 'starts in Unit 1 Workbook - Would like to run from a separate File, and open a "cell B5" <name in="" cell="" a1="">Workbook

'MONTH:JULY
'1ST COPY ROUTINE
Windows("MainDatabase.xls").Activate
Worksheets(Range.(“Unit 1").Select 'This is what I would like to reference to "cell B5" instead
Range("A11:A28").Select 'this never changes
Selection.Copy
ThisWorkbook.Activate 'Returns to Unit 1 Workbook - Would like to return to "cell B5" workbook instead (all of them have the same layout)
Sheets("JULY").Select 'this never changes
Range("A11").Select
ActiveSheet.Paste

End Sub</name>


Hope my problem is well described. If not, I'm sorry, please ask and I'll gladly explain further.

It would be awesome if you could help me solve any of the two situations.

 

[tonyyy]

You might give this a try...

Sub MacroCopyInformation()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim UnitName As String

Set wb1 = ThisWorkbook
UnitName = wb1.Sheets(1).Range("B5").Value
Set wb2 = Workbooks("MainDatabase.xls")
Set wb3 = Workbooks(UnitName & ".xls")

wb2.Sheets(UnitName).Range("A11:A28").Copy Destination:=wb3.Sheets("July").Range("A11")

End Sub

The code should go into its own workbook, and the MainDatabase.xls and the Unit X workbooks should both be open. It's assumed the Unit X workbooks also have a .xls extension.

Cheers,

tonyyy

 

[AyuHI]

You might give this a try...

Sub MacroCopyInformation()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim UnitName As String

Set wb1 = ThisWorkbook
UnitName = wb1.Sheets(1).Range("B5").Value
Set wb2 = Workbooks("MainDatabase.xls")
Set wb3 = Workbooks(UnitName & ".xls")

wb2.Sheets(UnitName).Range("A11:A28").Copy Destination:=wb3.Sheets("July").Range("A11")

End Sub

The code should go into its own workbook, and the MainDatabase.xls and the Unit X workbooks should both be open. It's assumed the Unit X workbooks also have a .xls extension.

Cheers,

tonyyy

A HUGE thank you!!

Apart from solving my doubt, you transformed 8 lines of my code into 1, which made it run way faster.

 

[tonyyy]

You're welcome. Glad it worked out.

If speed is a concern, you might try replacing the .Copy line of code with the following...

wb3.Sheets("July").Range("A11:A28") = wb2.Sheets(UnitName).Range("A11:A28").Value

No copying and pasting, just setting one range equal to another.

Cheers,

tonyyy