Hey All,

Can someone please help with my problem as outlined below?

A macro "1" is opened from workbook "A"
macro "1" formats workbook "A" and then it opens workbook "B"
macro "1" then calls macro "2" which formats workbook "B" (the active workbook) and copies a selected range.

How can I then paste this selected range back into workbook "A"?

The problem is that when I save and close workbook "B" the copied range isnt available for the subsequently active workbook "A" to paste into. (I tried the PutInClipboard method but I cannot get it to work)

Is there a way to achieve this without resorting to passing variables (ie filenames) between the two macros or merging the two macros together?

Thanks

macro "2"
'highlight cells to be copied
Cells.Select
Selection.Find(What:="As of Date", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(1, 0).Select
LastRow = Range([C1], ActiveSheet.UsedRange).Rows.Count
Range(Cells(7, 3), Cells(LastRow, 3).End(xlToRight)).Select
Selection.Copy

'sub ends and then returns to the calling sub ie macro "1"

macro "1"
'Close workbook "2"
ActiveWorkbook.Save
ActiveWorkbook.Close False

Range("A2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False