Results 1 to 6 of 6

Cut and paste macro

This is a discussion on Cut and paste macro within the Excel Questions forums, part of the Question Forums category; I am attempting to cut and paste a fixed number of cells within a column to the first empty column ...

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    3

    Default Cut and paste macro

    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!

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    5,045

    Default Re: Cut and paste macro

    Quote Originally Posted by gws_gws View Post
    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.
    Code:
    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.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel.
    To open the VB editor, press Alt + F11
    To run code from the Excel window, press Alt + F8, double click macro name

  3. #3
    New Member
    Join Date
    Jul 2013
    Posts
    3

    Default Re: Cut and paste macro

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

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    5,045

    Default Re: Cut and paste macro

    Quote Originally Posted by gws_gws View Post
    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.
    Code:
    Cells(1, lastCol + 1).EntireColumn.Select
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel.
    To open the VB editor, press Alt + F11
    To run code from the Excel window, press Alt + F8, double click macro name

  5. #5
    New Member
    Join Date
    Jul 2013
    Posts
    3

    Default Re: Cut and paste macro

    Quote Originally Posted by JLGWhiz View Post
    It shouldn't remove the + if you are using it like this.
    Code:
    Cells(1, lastCol + 1).EntireColumn.Select
    Thank you.

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    5,045

    Default Re: Cut and paste macro

    You're welcome
    Regards, JLG
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel.
    To open the VB editor, press Alt + F11
    To run code from the Excel window, press Alt + F8, double click macro name

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com