Copy and Paste from One Workbook to Another

  • Thread starter Thread starter Bill Oertell
  • Start date Start date
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.
 
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.
 
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.
 
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
 
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
 
What am I supposed to say - that certainly is a lot shorter and more
efficient than mine?
 
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.
 
Back
Top