Vba code to paste the selected data in to another range started from particular column

1
votes

I am facing the problem of pasting data in my vba code mentioned below :

Range("B:AK").Select
Selection.Copy
Range("AN:BW").Paste

I need to copy the data with Range starting from column B to Column AK and paste it into columns starting from AN to BW. But I am getting error 1004. Kindly help me with the updated version of the code. and columns belong to the same worksheet.

3
vbaexcel

3 Answers

1
votes

Copy >> Paste is a 1-line command:

Range("B:AK").Copy Range("AN:BW")

Edit 1:

Dim LastRow As Long, LastCell As Range

With Worksheets("Sheet1") ' change to your sheet's name
    ' safest way to get the last row with data in column "B:AK"

    Set LastCell = .Columns("B:AK").Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

    If Not LastCell Is Nothing Then
        LastRow = LastCell.Row
    Else
        MsgBox "Error! worksheet is empty", vbCritical
        End
    End If

    .Range("B2:AK" & LastRow).Copy .Range("AN2")
End With
0
votes

Try:

Range("B:AK").Select
Selection.Copy
Range("AN").Select
Activesheet.Paste

If it doesn't work, try adding a row number to AN, like "AN1"

0
votes

Try this one

Range("$B$2:$AK$1048576").Select
Selection.Copy
Range("AN2").Select
ActiveSheet.Paste

Let me know if it helps.