Pasting all values from a sheet to other

[Brijesh]

Dear All

I have two sheets in excel workbook. "Output" and "Output_New". Both sheets have exactly same format with lots of cells merged at many places in sheets. I have to paste all values of sheet "Output" on sheet "Output_New" using vba code.

I am using following code

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Output")
Set ws2 = ThisWorkbook.Sheets("Output_New")

ws1.Range(ws1.UsedRange.Address).Copy
ws2.Range("a1").PasteSpecial xlPasteValues

When i run this macro it gives following error
Run time error 1004
This operation requires the merged cells to be identically sized.

I know this error occurs bcoz the sheet have merged cells. But I don't know how to ovecome this error.

Thanks....

Regards

Brijesh

 

[Debraj]

Hi Brijesh..

Can we have the sample please..

 

[Debraj]

Or you can try this too..

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

ws1.Range(ws1.UsedRange.Address).Copy ws2.Range("a1")

 

[Brijesh]

Hi Debraj

I am attaching a sample file here. In this file I have to copy all value from sheet2 and to paste it on sheet4. I have to paste special values only not any formula. Both the sheets have same formatting.

 

[Debraj]

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet2")
Set ws2 = ThisWorkbook.Sheets("Sheet4")

ws2.Range("b6:P12").Value = ws1.Range("B6:P12").Value
End Sub

and another approach..

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet2")
Set ws2 = ThisWorkbook.Sheets("Sheet4")

ws1.Range(ws1.UsedRange.Address).Copy ws2.Range("A1")
ws2.UsedRange = ws2.UsedRange.Value
End Sub

 

[Brijesh]

Debraj

Your first approach will not be working perfectly because the range of data may vary time to time.

Your second approach is working fine with the sample file I have provided to you but it's not working properly in the file that I have to work for. Unfortunately I cannot provide the main file as it confidential.

 

[Brijesh]

Debraj

In your first approach you have selected a specified range: Range("B612")
Is there any way in which I can select entire range of any sheet or at least the range in which any data exists?