Results 1 to 5 of 5

Thread: Excel VBA to copy and paste columns

  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0

    Excel VBA to copy and paste columns

    Hi,

    I need some help. I have managed to obtain this pice of code which copies six columns with todays date into the next empty column. The date is a formula so will change to the next date. I need the code to copy and paste values/formats of todays date.

    Code:
    Sub CopyLastsixColumns()
    
        Dim lngLastRow     As Long
    
        With ThisWorkbook.Worksheets("Summary")
             lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
             Set fred = Cells.Find(What:=Date, After:=Range("A1"), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns)
             .Cells(1, .Columns.Count).End(xlToLeft).Offset(, -5).Resize(lngLastRow, 6).Copy .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1)
                
                 
        Range("A1").Select
        End With
    
    End Sub
    Thanks for your help in advance.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 02-24-2024 at 08:29 PM.

  2. #2
    Junior Member
    Join Date
    Apr 2015
    Posts
    1
    Rep Power
    0

    More details

    Quote Originally Posted by gripper View Post
    Hi,

    I need some help. I have managed to obtain this pice of code which copies six columns with todays date into the next empty column. The date is a formula so will change to the next date. I need the code to copy and paste values/formats of todays date.

    Code:
    Sub CopyLastsixColumns()
    
        Dim lngLastRow     As Long
    
        With ThisWorkbook.Worksheets("Summary")
             lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
             Set fred = Cells.Find(What:=Date, After:=Range("A1"), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns)
             .Cells(1, .Columns.Count).End(xlToLeft).Offset(, -5).Resize(lngLastRow, 6).Copy .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1)
                
                 
        Range("A1").Select
        End With
    
    End Sub
    Thanks for your help in advance.
    When you say 'copy six columns', are you referring to concatenating the values of each of the six into the next column?


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 02-24-2024 at 08:34 PM.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Or if you could show a sample and explain what you need, it would be easier for the volunteers to come up with the required code.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Feb 2013
    Posts
    5
    Rep Power
    0
    Hi,

    I got help from elsewhere, but for those who would like to know the answer, nere it is.

    Code:
    Sub newCpy()
        Dim lc As Integer
        Dim lr As Long
        Dim sh As Worksheet
         '
        lc = Range("IV1").End(xlToLeft).Column
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Set sh = Sheet1
         '
        sh.Range(Cells(1, lc - 5), sh.Cells(lr, lc)).Copy sh.Cells(1, lc + 1)
        sh.Range(Cells(1, lc - 5), sh.Cells(lr, lc)) = sh.Range(Cells(1, lc - 5), sh.Cells(lr, lc)).Value
    End Sub

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    This suffices:

    Code:
    With sheet1.cells(1).currentregion.rows(1)
       .offset(,.columns.count+1).resize(,6)=.offset(,.columns.count-5).resize(,6).value
    End With

Similar Threads

  1. Copy/Paste Excel Range/Chart into Powerpoint VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 03-13-2014, 02:59 PM
  2. Replies: 4
    Last Post: 12-12-2013, 06:16 PM
  3. Replies: 1
    Last Post: 07-10-2013, 11:38 AM
  4. Macro Copy Columns and Paste in rows.
    By TommyKris in forum Excel Help
    Replies: 3
    Last Post: 03-06-2013, 02:36 PM
  5. Replies: 2
    Last Post: 04-08-2012, 09:42 AM

Posting Permissions

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