Macro to copy a one worksheet in one book to the current workbook

[Frank Pytel]

Hello all;

I hope someone has done this already. I want to copy the contents of a
master file into the contents of an old workbook. I want to copy the entire
worksheet, formulas formatting and all.

I recorded this macro:

<--

Sub Copy()

Windows("BlankQuote.xls").Activate
Cells.Select
Selection.Copy
Windows("Q12345678.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

-->

It works great accept I need to reference workbook Q12345678.xls as
thisWorkbook. This workbook is the older workbook that I would like to update
the worksheet on if a client calls back. The other workbook will always be
BlankQuote.xls. This workbook will be kept up to date at all times.

Can anyone help me with this please?

Thank you.

God Bless

Frank Pytel

 

[JLGWhiz]

If I were doing it, I would not use the name ThisWorkbook because that is a
reserved name for Excel workbooks. Using reserved names can cause problems
when running code, because the compiler sees those names as costants that
represent predetermined objects. Maybe a name like "OriginalWB" would be a
better idea.

 

[FSt1]

hi
confused. why do you need to reference workbook Q12345678.xls as thisworkbook?

regards
FSt1

 

[JLGWhiz]

This would be a version that uses a variable OriginalWB to replace the
Windows("Q12345678.xls") refernce.

Sub Copy()
Dim OriginalWB As Workbook
Set OriginalWB = Worksbooks("Q12345678.xls")
Windows("BlankQuote.xls").Activate
Cells.Copy
OriginalWB.Range("A1").PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

 

[Frank Pytel]

Hello JLGWhiz;

I got an error at the word Worksbooks so I took out the 's'. Now I am
getting an error that states:

Runtime Error 438:

Object doesn't support this property or method.

When I click the Debug button it highlights everthing below:


OriginalWB.Range("A1").PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Is this saying that I cannot select the entire sheet and paste it to A1?

Thanks for your help, JLGWhiz. I sincerely appreciate it. Have a Great Day.

Frank Pytel

 

[Frank Pytel]

Hello FSt1;

I don't. I misstated myself. I want to reference this workbook as a
variable, not as 'thisworkbook'.

Thank you for getting back so quick FSt1. Have a Great Day.

Frank Pytel