R
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
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