switching between workbooks

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

Hi i have added the line at the bottom of your code

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
Workbooks("WorkBook1").Activate 'provided your workbook is name "Workbook1
ActiveSheet.Paste 'this will paste the selection on the activesheet at the activecell

I was able to just ACtivate the workbook because your Macro1 "close workbook" doesn't close the workbook it just saves it, however if you did close it you would have to use the Workbooks("Workbook1").Open but you would have to set the path if its the same path as workbook2 set it like With ThisWorkBook.Path......

Regards,
Simon

Hi Simon,

Thanks for the response.

As I mentioned I was trying to avoid this approach because the workbook name changes. Is there another way of achieving this?

Thanks

Originally Posted by dabith

Hi Simon,

Thanks for the response.

As I mentioned I was trying to avoid this approach because the workbook name changes. Is there another way of achieving this?

Thanks

Well depending on how many workbooks you have you could always use the workbooks(#) thingy to get the desired result.

For instance if you have only the 2 work books open you could do the flowing

i = 1
strCurrentBook = activeworkbook.name
do until i > workbooks.count

if workbooks(i).name <> strCurentBook then

exit do

end if

i = i +1
loop

workbooks(i).activate

and then whatever the code is for paste