Copy and Paste with Macro Between sheets

[jlclyde]

I have a macro that I am using to copy and paste information between
two workbooks. Since the original file is a txt file that I manually
convert to excel, I decided the best way to do this would be to save
the file, that way I coudl bounce back and forth between them. I have
2 other macros recored at the begining of my code but you can ignore
them. Please help.

Sub EnvelopeSR()
Application.Run "'sample report-do not throw-has instructions.xls'!
DeleteNonNumeric_ColB"
Application.Run "'sample report-do not throw-has instructions.xls'!
Delete_Rows"
ActiveWorkbook.SaveAs Filename:="Envelopes_SR.XLS"

Workbooks.Open Filename:="G:\Customer_Service\service reports\SR-
Template.xls"
Worksheets("Envelope Detail").Activate
Windows("Envelopes_SR.XLS").Activate
Range("A1:J15000").Copy
Windows("SR-Template.XLS").Activate
Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

The paste section fails every time.

Jay

 

[Dave Peterson]

Is this code in a General module or is it in a worksheet module?

Try moving it to a General module and try again.

You could try removing the .selects and .activates:

Option Explicit
Sub testme()

Dim TemplateDetailsWks as worksheet
Dim EnvelopeWks as worksheet

set templateDetailsWks _
= Workbooks.Open(Filename:= _
"G:\Customer_Service\service reports\SR-Template.xls") _
.worksheets("Envelope Detail")

set envelopewks = workbooks("Envelopes_SR.XLS") _
.worksheets("whatsheetnamehere")

templatedetailswks.range("A1:J15000").Copy

envelopewks.range("a5").pasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

But you could use the text file (without saving as a normal xls file) directly.

For instance, if the envelope worksheet is the activesheet when the macro
starts, you could use:

set envelopewks = activesheet

I bet you'll still want to save this data into a normal excel workbook file
later, though.

ps. That sample code is untested and uncompiled--watch for typos!