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.
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.
Last edited: