Copy user selected range from all open workbooks

[rsphorler]

I'm trying to write a macro which will prompt the user to select a
range then copy that same range from all open workbooks, (it will
always be worksheet 1). At the moment i have this code:

Dim MyRange As Range
Set MyRange = Application.InputBox(Prompt:="Select any range",
Title:="Demo", Type:=8)
MyRange.Select
MsgBox MyRange

For a = 2 To (Workbooks.Count - 1)
Workbooks(a).Activate
Range("A4:B4").Select
Selection.Copy
Workbooks(Summary).Activate
Range("A65536").End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.PasteSpecial
Workbooks(a).Activate
Workbooks(a).Worksheets(1).Range(MyRange).Select
Selection.Copy
Workbooks(Summary).Activate
Range("A65536").End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.PasteSpecial

The first part works which is to copy the date of the workbook (Cells
A4:B4) but the next part does not work, i'm guessing the whole range
information is "saved" to the MyRange variable.

any ideas

Richard

 

[OssieMac]

Hello Richard,

Need to get some things sorted out first before attempting the code.

"For a = 2 To (Workbooks.Count - 1)" You cannot be certain which workbook
will be the first workbook. I am assuming that you believe it will be the
workbook with your code but this might not always be the case and also you
cannot be certain of the order of the remaining workbooks. It is easy enough
to test for the workbook with the code (and I assume that is the one where
the data is being pasted) but not the order of the remaining workbooks.

If the order of the workbooks and hense the order of the data being pasted
does not matter then the order of the remaining workbooks is not a problem.
However, if the order of the data being pasted must follow a particular order
of workbooks then it needs to be handled.

If the workbook names follow a pattern like MyBook1.xls, MyBook2.xls,
MyBook3.xls then that can be handled. Otherwise you need a list of the
workbook names probably in a separate worksheet of the workbook with the code.

When you say will always be Sheet1. Do you mean like Worksheet(1) which will
always be the worksheet tab on the left or do you mean CodeName Sheet1.
(Lookup codename in help if you are not clear on this.)

When you answer these questions, I am sure I can handle the code for you.

 

[rsphorler]

Hi

The workbook with the macro in it is the personal.xls so this i
believe will always be the first workbook. The way i run this macro so
far is to open Excel ,open the files i need to summarise (these, at
the moment, are identical one sheet workbooks) then run the macro. It
then creates a new workbook called summary and then copies the data
from all of the files (the order does not matter as the end result is
to average the data and plot a chart)

Thanks for any help you can provide

Richard