Opening and pasting in active workbook

[TR]

I am having trouble pasting the data from an opened .xls file into the
current sheet I am on.
I have opened the workbook I need, and then I am attempting to paste
the data into the workbook I am currently using. I have no trouble
pasting the data into a different sheet on the workbook, just not the
worksheet titled 'targetrenewal'.

Does anyone know what I am doing wrong?

Thanks!

Sub PW_Open()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Wk1 As Worksheet
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wk1 = ActiveSheet
Wk1.Name = "TargetRenewal"
Set Wb2 = Workbooks.Open("C:\target rent\pw_targetrent.xls")
Wb2.Sheets("Sheet1").Copy _
Destination:=Wb1.Worksheets("TargetRenewal") ****this gives an
error 'Application-defined or Object-defined Error'*****
Wb2.Close False
Application.ScreenUpdating = True
End Sub

 

[Nigel]

After you open the 2nd workbook (it is the active workbook now) use the
following to copy the entire sheet

Sheets("Sheet1").Activate
Cells.Copy Destination:=Wb1.Sheets("TargetRenewal").Range("A1")

I could not get it to copy to the destination using references! but you do
need to specify the destination range it seems.
Hope this helps

Cheers
Nigel

 

[Norman Jones]

Hi TR,

Wb2.Sheets("Sheet1").Copy _
Destination:=Wb1.Worksheets("TargetRenewal")

You have not defined the range that you want to copy and you have not
defined a target range.

Assuing that you want to copy the range "A1100" from Sheet1 in
TargetRent.xls to the TargetRenewal sheet in the active workbook, then
change the above line to:

Wb2.Sheets("Sheet1").Range("A1100").Copy _
Destination:=Wk1.Range("A1")

If, alternatively, you want to copy the whole sheet, this becomes:

Wb2.Sheets("Sheet1").UsedRange.Copy _
Destination:=Wk1.Range("A1")

Amend the two ranges to suit your needs.

Incidentally, I have replaced

Wb1.Worksheets("TargetRenewal")

with

Wk1

as you have already assigned the sheet to this object variable. It is not
wrong to refer to the worksheet in the first way, it is merely unnecessary.

 

[TR]

Thanks!!!! It works great!!!!

Hi TR,


You have not defined the range that you want to copy and you have not
defined a target range.

Assuing that you want to copy the range "A1100" from Sheet1 in
TargetRent.xls to the TargetRenewal sheet in the active workbook, then
change the above line to:

Wb2.Sheets("Sheet1").Range("A1100").Copy _
Destination:=Wk1.Range("A1")

If, alternatively, you want to copy the whole sheet, this becomes:

Wb2.Sheets("Sheet1").UsedRange.Copy _
Destination:=Wk1.Range("A1")

Amend the two ranges to suit your needs.

Incidentally, I have replaced

Wb1.Worksheets("TargetRenewal")

with

Wk1

as you have already assigned the sheet to this object variable. It is not
wrong to refer to the worksheet in the first way, it is merely unnecessary.