Copy and Paste from One Workbook to Another
 

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.

Copy and Paste from One Workbook to Another
 

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.


In article ,
"Bill Oertell" wrote:

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.

Copy and Paste from One Workbook to Another
 

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.



"J.E. McGimpsey" wrote in message
...
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.


In article ,
"Bill Oertell" wrote:

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.

Copy and Paste from One Workbook to Another
 

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

--
Regards,
Tom Ogilvy


"Bill Oertell" wrote in message
...
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.



"J.E. McGimpsey" wrote in message
...
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.


In article ,
"Bill Oertell" wrote:

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.

Copy and Paste from One Workbook to Another
 

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("ThisW orkbook").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("ThatW orkbook").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

Copy and Paste from One Workbook to Another
 

That didn't paste right.
"Bill Oertell" wrote in message
...

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

CurrentFile = ActiveWorkbook.Name
SourceNumRows
=Application.WorksheetFunction.CountA(Sheets("This Workbook").Range("AA20:AA4
9"))
Workbooks.Open "T:\ThatWorkbook.xls"
Windows("ThatWorkbook.xls").Activate
Do
Loop While Windows("ThatWorkbook.xls").Activate = False
DestNumRows =
Application.WorksheetFunction.CountA(Sheets("ThatW orkbook").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

End Sub

Copy and Paste from One Workbook to Another
 

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

--
Regards,
Tom Ogilvy


Bill Oertell wrote in message
...
That didn't paste right.
"Bill Oertell" wrote in message
...

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

CurrentFile = ActiveWorkbook.Name
SourceNumRows

=Application.WorksheetFunction.CountA(Sheets("This Workbook").Range("AA20:AA4
9"))
Workbooks.Open "T:\ThatWorkbook.xls"
Windows("ThatWorkbook.xls").Activate
Do
Loop While Windows("ThatWorkbook.xls").Activate = False
DestNumRows =

Application.WorksheetFunction.CountA(Sheets("ThatW orkbook").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

End Sub

Copy and Paste from One Workbook to Another
 

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.

"Tom Ogilvy" wrote in message
...
What am I supposed to say - that certainly is a lot shorter and more
efficient than mine?

--
Regards,
Tom Ogilvy


Bill Oertell wrote in message
...
That didn't paste right.
"Bill Oertell" wrote in message
...

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

CurrentFile = ActiveWorkbook.Name
SourceNumRows


=Application.WorksheetFunction.CountA(Sheets("This Workbook").Range("AA20:AA4
9"))
Workbooks.Open "T:\ThatWorkbook.xls"
Windows("ThatWorkbook.xls").Activate
Do
Loop While Windows("ThatWorkbook.xls").Activate = False
DestNumRows =


Application.WorksheetFunction.CountA(Sheets("ThatW orkbook").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

End Sub