Copy and Paste from One Workbook to Another

B

Bill Oertell

How can I copy from one workbook, open another and paste to it. IOW, say
I've got a workbook named ThisWorkbook and want to paste its content to
ThatWorkbook. To complicate matters the code needs to figure out what range
to copy and where to start the paste in ThatWorkbook.
 
J

J.E. McGimpsey

You don't give enough information. What is insufficient about

ThisWorkbook.SaveCopyAs("ThatWorkbook")

that you'd need to copy and paste?

If the code needs to figure things out, you'll need to give us
enough clues to figure them out, too.
 
B

Bill Oertell

Sorry. I know but my OE has problems. It crashes before I can post a
complete message.

What I want to do is copy from ThisWorkbook a range that has entries and
paste it to the end of ThatWorkbook, which already has some entries in it.
I would already have ThisWorkbook open and would want the macro to open
ThatWorkbook, which I'm using for record keeping, and copy the range that
has entries in ThisWorkbook and paste it after the last entry in
ThatWorkbook.
 
T

Tom Ogilvy

Dim wkbk as Workbook, wkbk1 as Workbook
Dim rng as Range
set wkbk = Workbooks("SourceBook.xls")
set wkbk1 = Workbooks.Open "C:\DestBook.xls"
set rng = wkbk1.Worksheets(1).Cells(rows.count,1).End(xlup)(2)
wkbk.Worksheets(1).Range("A1").CurrentRegion.Copy _
Destination:=rng
 
B

Bill Oertell

I actually came up with the following bit of code:

Sub CopyToArchive()
Dim SourceNumRows As Integer
Dim DestNumRows As Integer
Dim CurrentFile As String
Dim Requester As String

CurrentFile = ActiveWorkbook.Name
Requester = Cells(4, 22)
SourceNumRows =
Application.WorksheetFunction.CountA(Sheets("ThisWorkbook").Range("AA20:AA49
"))
Workbooks.Open "T:\ThatWorkbook.xls"
Windows("ThatWorkbook.xls").Activate
Do
Loop While Windows("ThatWorkbook.xls").Activate = False
DestNumRows =
Application.WorksheetFunction.CountA(Sheets("ThatWorkbook").Range("AA3:AA150
0"))
Windows(CurrentFile).Activate
ActiveSheet.Range("I20:AR" & SourceNumRows + 19).Copy
Windows("ThatWorkbook.xls").Activate
ActiveSheet.Range("I" & DestNumRows + 3).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Cells(DestNumRows + 3, 45) = Requester
Cells(DestNumRows + 3, 46) = Application.UserName
Cells(DestNumRows + 3, 47) = Date
Range("AT" & DestNumRows).Select

End Sub
 
T

Tom Ogilvy

What am I supposed to say - that certainly is a lot shorter and more
efficient than mine?
 
B

Bill Oertell

Hope I can finish this before Outlook Express crashes.
I wrote this code before I saw your reply, Tom. I'm sure yours was more
compact than mine, but hey, it works.
 
Top