Cut and paste macro

[gws_gws]

I am attempting to cut and paste a fixed number of cells within a column to the first empty column in the sheet. The marco does this, deletes the copied column, and repeats. It works most of the time. Randomly it will paste the cells to a column that is already populated with data.

Here is the code:

Sub Macro1()
'
' Macro1 Macro
'

'

Dim kolumn As Long

Range("C64:C125").Select
Selection.Copy
Range("C1").Select
kolumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, kolumn).EntireColumn.Select
ActiveSheet.Paste
Rows("64:125").Select
Selection.Delete Shift:=xlUp


End Sub


Any suggestions would be greatly appreciated!

 

[JLGWhiz]

I am attempting to cut and paste a fixed number of cells within a column to the first empty column in the sheet. The marco does this, deletes the copied column, and repeats. It works most of the time. Randomly it will paste the cells to a column that is already populated with data.

Here is the code:

Sub Macro1()
'
' Macro1 Macro
'

'

Dim kolumn As Long

Range("C64:C125").Select
Selection.Copy
Range("C1").Select
kolumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, kolumn).EntireColumn.Select
ActiveSheet.Paste
Rows("64:125").Select
Selection.Delete Shift:=xlUp


End Sub


Any suggestions would be greatly appreciated!

This statement:

kolumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1

only finds the next empty cell on row 1. It does not guarantee that the entire column is blank. If you want a more reliable code to find the last empty column, then try this one. Just make sure there is a value in cell A1 or it will error.

lastCol = ActiveSheet.Cells.Find(What:="*", After:=ActiveSheet.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

Then use "lastCol + 1" for the empty column.

 

[gws_gws]

It does not like LastCol + 1. Editor removes the + sign after I type it.

 

[JLGWhiz]

It does not like LastCol + 1. Editor removes the + sign after I type it.

It shouldn't remove the + if you are using it like this.

Cells(1, lastCol + 1).EntireColumn.Select

 

[gws_gws]

It shouldn't remove the + if you are using it like this.

Cells(1, lastCol + 1).EntireColumn.Select

Thank you.

 

[JLGWhiz]

You're welcome
Regards, JLG