past method error
 

This is my first go at vb programming with excel. I have code attached to a
pushbutton to copy a sheet in the current workbook, open an existing workbook
and pasting the copied info over the data in a sheet called "pricing". My
code is below. I am getting a run-time err '1004': Paste method of Worksheet
class failed.
It does paste the info but doesn't finish the routine.

Any help would be greatly appreciated.

Private Sub CommandButton1_Click()

Sheets("Sheet3").Select
Selection.Copy
Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
Sheets("pricing").Activate
ActiveSheet.Unprotect Password:="xxxx"
ActiveSheet.Paste
ActiveSheet.Protect Password:="xxxx"
Sheets("Glossary").Select

End Sub


--
kr

past method error
 

Give this a try...

Private Sub CommandButton1_Click()

Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
with Workbooks("Pricelist2.xls").Sheets("pricing")
.Activate
.Unprotect Password:="xxxx"
Thisworkbook.Sheets("Sheet3").Cells.Copy Desination:= .Cells
.Protect Password:="xxxx"
end with
Workbooks("Pricelist2.xls").Sheets("Glossary").Sel ect

End Sub

--
HTH...

Jim Thomlinson


"kr" wrote:

This is my first go at vb programming with excel. I have code attached to a
pushbutton to copy a sheet in the current workbook, open an existing workbook
and pasting the copied info over the data in a sheet called "pricing". My
code is below. I am getting a run-time err '1004': Paste method of Worksheet
class failed.
It does paste the info but doesn't finish the routine.

Any help would be greatly appreciated.

Private Sub CommandButton1_Click()

Sheets("Sheet3").Select
Selection.Copy
Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
Sheets("pricing").Activate
ActiveSheet.Unprotect Password:="xxxx"
ActiveSheet.Paste
ActiveSheet.Protect Password:="xxxx"
Sheets("Glossary").Select

End Sub


--
kr

past method error
 

Thanks Jim, but now I am getting "Application-defined or object-defined
error". The debugger highlights the line beginning "ThisWorkbook.Sheets
etc..."

--
kr


"Jim Thomlinson" wrote:

Give this a try...

Private Sub CommandButton1_Click()

Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
with Workbooks("Pricelist2.xls").Sheets("pricing")
.Activate
.Unprotect Password:="xxxx"
Thisworkbook.Sheets("Sheet3").Cells.Copy Desination:= .Cells
.Protect Password:="xxxx"
end with
Workbooks("Pricelist2.xls").Sheets("Glossary").Sel ect

End Sub

--
HTH...

Jim Thomlinson


"kr" wrote:

This is my first go at vb programming with excel. I have code attached to a
pushbutton to copy a sheet in the current workbook, open an existing workbook
and pasting the copied info over the data in a sheet called "pricing". My
code is below. I am getting a run-time err '1004': Paste method of Worksheet
class failed.
It does paste the info but doesn't finish the routine.

Any help would be greatly appreciated.

Private Sub CommandButton1_Click()

Sheets("Sheet3").Select
Selection.Copy
Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
Sheets("pricing").Activate
ActiveSheet.Unprotect Password:="xxxx"
ActiveSheet.Paste
ActiveSheet.Protect Password:="xxxx"
Sheets("Glossary").Select

End Sub


--
kr

past method error
 

Sorry Typo...

Desination:= .Cells
Should Be
Destination:= .Cells
--
HTH...

Jim Thomlinson


"kr" wrote:

Thanks Jim, but now I am getting "Application-defined or object-defined
error". The debugger highlights the line beginning "ThisWorkbook.Sheets
etc..."

--
kr


"Jim Thomlinson" wrote:

Give this a try...

Private Sub CommandButton1_Click()

Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
with Workbooks("Pricelist2.xls").Sheets("pricing")
.Activate
.Unprotect Password:="xxxx"
Thisworkbook.Sheets("Sheet3").Cells.Copy Desination:= .Cells
.Protect Password:="xxxx"
end with
Workbooks("Pricelist2.xls").Sheets("Glossary").Sel ect

End Sub

--
HTH...

Jim Thomlinson


"kr" wrote:

This is my first go at vb programming with excel. I have code attached to a
pushbutton to copy a sheet in the current workbook, open an existing workbook
and pasting the copied info over the data in a sheet called "pricing". My
code is below. I am getting a run-time err '1004': Paste method of Worksheet
class failed.
It does paste the info but doesn't finish the routine.

Any help would be greatly appreciated.

Private Sub CommandButton1_Click()

Sheets("Sheet3").Select
Selection.Copy
Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
Sheets("pricing").Activate
ActiveSheet.Unprotect Password:="xxxx"
ActiveSheet.Paste
ActiveSheet.Protect Password:="xxxx"
Sheets("Glossary").Select

End Sub


--
kr

past method error
 

I am good at typos also ~ note my description "Past method error" instead of
"Paste method error"

Thanks so much for your help, I was about to pull my hair out trying
different things.

--
kr


"Jim Thomlinson" wrote:

Sorry Typo...

Desination:= .Cells
Should Be
Destination:= .Cells
--
HTH...

Jim Thomlinson


"kr" wrote:

Thanks Jim, but now I am getting "Application-defined or object-defined
error". The debugger highlights the line beginning "ThisWorkbook.Sheets
etc..."

--
kr


"Jim Thomlinson" wrote:

Give this a try...

Private Sub CommandButton1_Click()

Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
with Workbooks("Pricelist2.xls").Sheets("pricing")
.Activate
.Unprotect Password:="xxxx"
Thisworkbook.Sheets("Sheet3").Cells.Copy Desination:= .Cells
.Protect Password:="xxxx"
end with
Workbooks("Pricelist2.xls").Sheets("Glossary").Sel ect

End Sub

--
HTH...

Jim Thomlinson


"kr" wrote:

This is my first go at vb programming with excel. I have code attached to a
pushbutton to copy a sheet in the current workbook, open an existing workbook
and pasting the copied info over the data in a sheet called "pricing". My
code is below. I am getting a run-time err '1004': Paste method of Worksheet
class failed.
It does paste the info but doesn't finish the routine.

Any help would be greatly appreciated.

Private Sub CommandButton1_Click()

Sheets("Sheet3").Select
Selection.Copy
Workbooks.Open Filename:="G:\PriceLists\PriceList2.xls"
Workbooks("Pricelist2.xls").Activate
Sheets("pricing").Activate
ActiveSheet.Unprotect Password:="xxxx"
ActiveSheet.Paste
ActiveSheet.Protect Password:="xxxx"
Sheets("Glossary").Select

End Sub


--
kr