CODEWITHSUNDEEP
excelvba
Mortend
Mortend

Reputation: 15

Macro to copy and paste 5 last columns problems

I've just written a macro to find the last 5 columns with data and then copy and paste this preceeding the last column. I built this macro in a dummy document so not to corrupt my current file and got the macro working fine with the below code:

Sub CopyLastFiveRows()

LastColumn = Sheets("Sheet2").Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
On Error GoTo 0

Sheets("Sheet2").Columns(LastColumn - 4).Resize(, 5).Select
Selection.Copy

'   Enter the rest of your paste code here

Sheets("Sheet2").Columns(LastColumn + 1).Select
ActiveSheet.Paste

End Sub

When I cut and paste the above if in my actual working file it works fine if the data tab name is "Sheet2", however if Im to change the tab name to "NFG" and replace all "Sheet2" with this in the macro above I get a "run-time error '1004' Select method of Range class failed". A bit confused. If anyone could help me out that'd be great. Thanks.

NOTE - Other thing I forgot to mention was that Im going to assign this macro to a button on a front sheet of the workbook. I think this might be causing the problem as this is the only variable I can think of between the documents I've tested macro on.

Upvotes: 0

Views: 2307

Answers (2)

Transformer
Transformer

Reputation: 389

Use the following code.Hope it will work. 

Sub CopyLastFiveColumns()

        With ThisWorkbook.Worksheets("NFG")                 
             .Range(.Cells(1, .Columns.Count).End(xlToLeft), .Cells(1, .Columns.Count).End(xlToLeft).Offset(, -4)).Copy .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1)
        End With

 End Sub

Upvotes: 1

Transformer
Transformer

Reputation: 389

Use the following code to copy all rows of last five columns

Sub CopyLastFiveColumns()

    Dim lngLastRow     As Long

    With ThisWorkbook.Worksheets("NFG")
         lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
         .Cells(1, .Columns.Count).End(xlToLeft).Offset(, -4).Resize(lngLastRow, 5).Copy .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1)
    End With

End Sub

Upvotes: 1

Related Questions