Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

copy contents from one sheet to another worksheet of another workbook

Hi Experts,

I tried to copy one work sheet sheet1 to another worksheet but getting runtim error:

"Object does not support this property of method"


Set ws_Source_02 = wb_Source_01.Sheets("Sheet1")
Set ws_Source_03 = wb_Source_02.Sheets("Template")

ws_Source_03("Template").Cells.Copy Destination:=ws_Source_02("Sheet1").Cells

Would you please advise how to fix to get rid of this error.

Thanks
0
alam747
Asked:
alam747
  • 4
  • 3
1 Solution
 
alam747Author Commented:
I tried this :

ws_Source_03.Cells.UsedRange.Copy ws_Source_02.Cells.Range("A1")

getting same run time error.
0
 
byundtCommented:
I'm guessing that ws_Source_03 and ws_Source_02 are both Dim as Worksheet. If so, then you might try:
ws_Source_03.Cells.Copy Destination:=ws_Source_02.Cells(1, 1)

Open in new window

The above statement copies the formats, formulas & values on the Template sheet onto Sheet1. Did you really mean to wipe out any data that had been on Sheet1? Or were you really wanting to apply the formats from Template to Sheet1. In such a case you might do:
ws_Source_03("Template").Cells.Copy 
ws_Source_02("Sheet1").Cells(1, 1).PasteSpecial xlPasteFormats

Open in new window

0
 
alam747Author Commented:
I want data and the format,
First code copies the data but not the cell format such as cell width, cell etc.

Second code only copies the format after I changed to:
ws_Source_03.Cells.Copy
ws_Source_02.Cells(1, 1).PasteSpecial xlPasteFormats

because, if I use as is got the same run time error mentioned.

To copy both data and format what will be code.

Please advise.

Thanks
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
byundtCommented:
My goof on not deleting the "Sheet1" and "Template" from the second example.

Do you want to copy formulas, or just values?
ws_Source_03.Cells.Copy
ws_Source_02.Cells(1, 1).PasteSpecial xlPasteAll    'Formats, formulas and values
ws_Source_02.Cells(1, 1).PasteSpecial xlPasteValues     'Overwrite formulas with the values they originally returned

Open in new window

0
 
alam747Author Commented:
if I want to add formula to one of cell after assign other cell value such as:

ws_Source_02.Cells(2,3) = 40
ws_Source_02.Cells(2.4) = 20

how can I assign a formula in ws_Source_02.Cells(e:e)   like =C2-d2

please advise

Thanks
0
 
byundtCommented:
Here are a few alternatives:
Sub test()
Dim ws_Source_02 As Worksheet
Set ws_Source_02 = ActiveSheet
ws_Source_02.Range("E2").Formula = "=C2-D2"
With ws_Source_02
    .Range(.Cells(2, 5), .Cells(.Cells(.Rows.Count, 4).End(xlUp).Row, 5)).Formula = "=C2-D2"
    .Range("E:E").FormulaR1C1 = "=RC[-2]-RC[-1]"
End With
End Sub

Open in new window

0
 
alam747Author Commented:
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now