Find the last column that contains data, and copy and paste it

[carefreeant88]

Good morning,

Struggling a bit with one of my Macros, and hoping you guys can help again?

I basically need to achieve the following:

1. Search for the sheet called 'Sales Plan Review'
2. Insert a blank column between columns A and B
3. On that same sheet, ('Sales Plan Review') go to the last column that contains any data
4. Once that column is found, copy the whole column
5. Paste the data from that column, into the blank column that you inserted between A and B
   

Any help would be greatly appreciated!

Thanks

 

[Joe4]

In determining where the last column of data is, does every column with data have a title/header in row 1?
Also, do you want to actually move the whole column, or just copy it (Cut vs. Copy)?

 

[kvsrinivasamurthy]

Try.

Sub CopyLastColumn()
Dim Clm&
Sheets("Sales Plan Review").Activate
Range("B1").EntireColumn.Insert
Clm = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1
Cells(1, Clm).EntireColumn.Copy Range("B1")
End Sub

 

[Joe4]

If my assumption about headers in row 1 in accurate, and you just want to copy the last column (and not remove the data you are copying from the last column), this code should do what you want:

Sub MyMoveDataMacro()

'   Go to Sales Plan Review sheet
    Sheets("Sales Plan Review").Select
    
'   Copy last column in between columns A and B
    Cells(1, Columns.Count).End(xlToLeft).EntireColumn.Copy
    Columns("B:B").Insert Shift:=xlToRight
    Application.CutCopyMode = False

End Sub

If you actually want to move the data instead of copying it (so it no longer appears in the last column),
change the ".Copy" to ".Cut"

 

[carefreeant88]

If my assumption about headers in row 1 in accurate, and you just want to copy the last column (and not remove the data you are copying from the last column), this code should do what you want:

Sub MyMoveDataMacro()

'   Go to Sales Plan Review sheet
    Sheets("Sales Plan Review").Select
   
'   Copy last column in between columns A and B
    Cells(1, Columns.Count).End(xlToLeft).EntireColumn.Copy
    Columns("B:B").Insert Shift:=xlToRight
    Application.CutCopyMode = False

End Sub

If you actually want to move the data instead of copying it (so it no longer appears in the last column),
change the ".Copy" to ".Cut"

Superb, thank you so much

 

[Joe4]

You are welcome.
Glad we were able to help!