Copy last used column to next available

[VegasNath]

I am trying to copy the last used column to the next column over, something like........ (without success)

Code: Select all

iCol = Mid(Cells(1, 256).End(xlToLeft).Columns.Address, 2, 1)
    wb2.Sheets("Report").Range(iCol & "1:" & iCol & "1000").Copy Destination:=(wb2.Sheets("Report").Range(iCol + 1 & "1"))

I guess that I cannot "add 1" to iCol as iCol is a letter (string), not a number, but I am unsure how to achieve the result?

Also tried:

Code: Select all

iCol = Mid(Cells(1, 256).End(xlToLeft).Columns, 1, 1)
    wb2.Sheets("Report").Columns(iCol).Copy Destination:=wb2.Sheets("Report").Columns(iCol) + 1

[VegasNath]

I got there in the end.....

Code: Select all

    Dim i As Integer
    i = wb2.Sheets("Report").Range("IV1").End(xlToLeft).Column
    wb2.Sheets("Report").Columns(i).Copy Destination:=wb2.Sheets("Report").Columns(i + 1)

[VegasNath]

Where's the button to give myself a

[VegasNath]

I got there in the end.....

Code: Select all

    Dim i As Integer
    i = wb2.Sheets("Report").Range("IV1").End(xlToLeft).Column
    wb2.Sheets("Report").Columns(i).Copy Destination:=wb2.Sheets("Report").Columns(i + 1)

AAAaaaaaaaaaaargh. I still need to refer to the column letter. How can i do that?

[VegasNath]

I got there in the end.....

Code: Select all

    Dim i As Integer
    i = wb2.Sheets("Report").Range("IV1").End(xlToLeft).Column
    wb2.Sheets("Report").Columns(i).Copy Destination:=wb2.Sheets("Report").Columns(i + 1)

AAAaaaaaaaaaaargh. I still need to refer to the column letter. How can i do that?

Probably not the best approach, but this works:

Code: Select all

Dim i As Integer
Dim iCol As String

    If Mid(Cells(1, 256).End(xlToLeft).Columns.Address, 4, 1) <> "$" Then
        iCol = Mid(Cells(1, 256).End(xlToLeft).Columns.Address, 2, 1)
    Else
        iCol = Mid(Cells(1, 256).End(xlToLeft).Columns.Address, 2, 2)
    End If

    i = wb2.Sheets("Report").Range("IV1").End(xlToLeft).Column
    wb2.Sheets("Report").Columns(i).Copy Destination:=wb2.Sheets("Report").Columns(i + 1)
    wb2.Sheets("Report").Range(iCol & "1").Offset(0, 1).Interior.ColorIndex = xlNone
    wb2.Sheets("Report").Range(iCol & "1").Offset(0, 1).ClearComments

[HansV]

There's no need to know the column letter(s). Instead of Range(iCol & "1"), you can use Cells(1, i).

[VegasNath]

Thanks Hans, much simpler!