Pasting Between Workbooks

B

bodhisatvaofboogie

Okay, my macro requires opening two extra workbooks for sorting/copy/pasting
purposes, and I am working on a way to close the extras. This formula Closes
Workbook 1 which leaves Workbook 2 active, I do a Cells.Select, copy and
activate next which brings up the original workbook, then select A1 and
paste. Well the code always stops at the Line:

ActiveSheet.Paste

So what am I doing wrong?

I was going to then follow it with Wiindows("Book2").Activate and have it
close after pasting the good data over all the original document data
essentially leaving me with the single work book open. Make sense? Here is
the code:

Windows("Book1").Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False
Cells.Select
Selection.Copy
Windows("Book2").ActivateNext
Cells.Select
Selection.AutoFilter
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = True

THANKS!!!
 
J

Jim Thomlinson

Trying to keep track of which book is active at any given time is problematic
and prone to failure. I recomend creating workbook objects to deal with the
book. Note that the book running the code is always ThisWorkbook. Note that
you are best off to specify which sheets you are dealing with explicitly and
not relying on what is the active sheet at the time. Otherwise a small change
in coding upstream can have disasterous effects later on.

dim wbk1 as workbook
dim wbk2 as workbook

set wbk1 = workbooks("Book1.xls")
set wbk2 = workbooks("Book2.xls")

wbk1.close SaveChanges:=false
wbk2.Sheets("Sheet1").cells.copy thisworkbook.sheets("Sheet1").Cells
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top