create a macro to copy data from 50 always uniquely named sheets in files sent to me to 50 sheets in another file

[marcrb]

Hi. Here's the situation: I am new in the job and new in the job stream. I am the proverbial new kid on the block and I am inheriting this process. There are people upstream and downstream that use the excel files I get so I cannot make any real changes (at this time).

Here's my problem. Each day I am sent multiple files that have 50 sheets in them. Every sheet always has a unique name. I need to copy just a small area of each of the 50 sheets into a "Management Summary" file. Right now they manually copy the areas needed from each sheet (tab) individually and post it into the Management Summary file one sheet at a time.

I'd like to use a macro to copy the area from each sheet to each of the 50 sheets in a Management Summary file (very large complicated file).

I'm trying to figure out how to define a macro to copy the defined area from 50 sheets that ALWAYS have different (unique) names into the Management Summary file. Seems pretty straightforward but because the sheets are always named differently it is a bit more of a challenge. I hope my question is clear.

Thanks very much!

 

[My Aswer Is This]

The names of the sheets are not important if we are dealing with all the sheets. We just tell the script to copy and paste the defined range in all sheets. If we are not dealing with all sheets then that would be more difficult. What is the name of the Range to copy?
Will this be copied into new sheets on the Management file or on 50 existing sheets?

 

[marcrb]

The range was never named.

I will name the range "Mgt Sum". So the macro would copy "Mgt Sum" from one of each of the 50 sheets in the "from" file and paste it each to one of each of the 50 sheets in the "to" file sequentially.

 

[My Aswer Is This]

If I understand it correctly the two workbook names will be “From” and "To” is that correct?
On the file “To” sheet1 I’m assuming we will pasting this into range “Mgt_Sum”
The range name cannot have spaces. The name must be “Mgt_Sum”
So in conclusion we will be copying data from:
Workbook(“From.xlsm”) sheet1 range “Mgt_Sum”

And be pasting this data into:
Workbook(“To.xlsm”) sheet1 range “Mgt_Sum”

Is all of this correct. I have the script written but I need to assure I’m correct.

 

[My Aswer Is This]

Marcrb.
We cannot use a named range we will need to use a range sort of like this:
Range("A1:B30"). Please give me the range we will be working with.

 

[marcrb]

Marcrb.
We cannot use a named range we will need to use a range sort of like this:
Range("A1:B30"). Please give me the range we will be working with.

The range is Range("A1:E57") if I am using the correct syntax. Thank you so much!

 

[My Aswer Is This]

You did not answer my questions in post #4.
I understand now we will use Range ("A1:E57")
Please read post # 4 and answer my questions please.

 

[marcrb]

You did not answer my questions in post #4.
I understand now we will use Range ("A1:E57")
Please read post # 4 and answer my questions please.

Thank you, Yes, you are correct.

The data from the
1st sheet (Note: the sheet name will ALWAYS be different every time I get the file)
in the "from" file range a1:e57
will be copied and pasted to the
1st sheet range a1:e57 in the "to" file.

then The data from the
2nd sheet (Note: the sheet name will ALWAYS be different every time I get the file)
in the "from" file range a1:e57
will be copied and pasted to the
2nd sheet range a1:e57 in the "to" file.

and on and on up to the 50th sheets respectively.

Thank you thank you thank you!

 

[My Aswer Is This]

Try this:
Install this script into the Workbook “From”
You will need to have both Workbooks “From” and “To” open when you run the script.
Be sure and test this on sample data first to insure it’s working the way you want.
I assume you know how to install scripts and run them. If you need instructions on how to do this let me know.

Sub Script_Me()
'marcrb
'version 1
Dim i As Integer
For i = 1 To 50
Sheets(i).Range("A1:E57").Copy Destination:=Workbooks("To.xlsm").Sheets(i).Range("A1:E57")
Next
End Sub

 

[marcrb]

You are the awesome-est!

Thanks a ton My Answer is This!

marcrb