Macro which Select Sheet instead of Range

[vasudev_kamat]

Hi Friends, I have a macro which is selecting range, below is the code. However my requirement is macro to copy full sheet instead of range and paste in another sheet. My data in range starting A2 to D9 in column. Row ending range keep changing. Please help.

Sub Copy_PasteSpecial_Method()

'Copy range to clipboard
Workbooks("New-Data.xlsx").Worksheets("Export").Range("A2:D9").Copy

'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues

End Sub

Regards
Vasudev

 

[Michael M]

Maybe this
Sub Copy_PasteSpecial_Method() dim lr as long lr= sheets("Export")cells(rows.count,"D").end(xlup).row 'Copy range to clipboard Workbooks("New-Data.xlsx").Worksheets("Export").Range("A2:D" & lr).Copy 'PasteSpecial to paste values, formulas, formats, etc. Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues End Sub

 

[vasudev_kamat]

Maybe this
Sub Copy_PasteSpecial_Method() dim lr as long lr= sheets("Export")cells(rows.count,"D").end(xlup).row 'Copy range to clipboard Workbooks("New-Data.xlsx").Worksheets("Export").Range("A2:D" & lr).Copy 'PasteSpecial to paste values, formulas, formats, etc. Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues End Sub

Hi , It is showing compile error.

Please help

 

[Michael M]

OOps sorry. Missed a period

Sub MM1() dim lr as long lr= sheets("Export").cells(rows.count,"D").end(xlup).row 'Copy range to clipboard Workbooks("New-Data.xlsx").Worksheets("Export").Range("A2:D" & lr).Copy 'PasteSpecial to paste values, formulas, formats, etc. Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues End Sub

 

[vasudev_kamat]

OOps sorry. Missed a period

Sub MM1() dim lr as long lr= sheets("Export").cells(rows.count,"D").end(xlup).row 'Copy range to clipboard Workbooks("New-Data.xlsx").Worksheets("Export").Range("A2:D" & lr).Copy 'PasteSpecial to paste values, formulas, formats, etc. Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues End Sub

Something wrong here bro. It is showing subscript out of range message. Even though all sheet names are correct.

 

[vasudev_kamat]

Something wrong here bro. It is showing subscript out of range message. Even though all sheet names are correct.

Hi Bro. Finally fixed the code.

Final one as follows. Thanks.

Sub MM1()
Dim lr As Long
lr = Workbooks("New-data.xlsx").Sheets("Export").Cells(Rows.Count, "V").End(xlUp).Row
'Copy range to clipboard
Workbooks("New-data.xlsx").Worksheets("Export").Range("A2:V" & lr).Copy

'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("test.xlsm").Worksheets("Data").Range("B49").PasteSpecial Paste:=xlPasteValues

End Sub

 

[vasudev_kamat]

Workbook was not defined, hence it was showing error.

 

[Michael M]

Are the workbook names also correct !!
Are both workbooks open ?
Try it this way

Sub MM1() Dim lr As Long lr = Sheets("Export").Cells(Rows.Count, "D").End(xlUp).Row 'Copy range to clipboard Workbooks("New-Data.xlsx"). Worksheets("Export").Range("A2:D" & lr).Copy 'PasteSpecial to paste values, formulas, formats, etc. With Workbooks("Reports.xlsm"). Worksheets("Data").Range("A2") .PasteSpecial Paste:=xlPasteValues End With End Sub

 

[vasudev_kamat]

Are the workbook names also correct !!
Are both workbooks open ?
Try it this way

Sub MM1() Dim lr As Long lr = Sheets("Export").Cells(Rows.Count, "D").End(xlUp).Row 'Copy range to clipboard Workbooks("New-Data.xlsx"). Worksheets("Export").Range("A2:D" & lr).Copy 'PasteSpecial to paste values, formulas, formats, etc. With Workbooks("Reports.xlsm"). Worksheets("Data").Range("A2") .PasteSpecial Paste:=xlPasteValues End With End Sub

Hi Michael Bro, Its fixed. I have defined workbook.

Sub MM1()
Dim lr As Long
lr = Workbooks("New-data.xlsx").Sheets("Export").Cells(Rows.Count, "V").End(xlUp).Row
'Copy range to clipboard
Workbooks("New-data.xlsx").Worksheets("Export").Range("A1:V" & lr).Copy

'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("test.xlsm").Worksheets("Data").Range("B49").PasteSpecial Paste:=xlPasteValues

End Sub

 

[vasudev_kamat]

Thanks a lot for support