Excel VBA to copy and paste columns

**gripper** · 04-01-2015, 05:55 AM

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

**Jim Snyder** · 04-06-2015, 10:09 PM

Originally Posted by gripper

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.

**Excel Fox** · 04-07-2015, 07:35 AM

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.

**gripper** · 04-08-2015, 01:47 AM

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

**snb** · 04-15-2015, 06:03 PM

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

Thread: Excel VBA to copy and paste columns

Thread Tools

Search Thread

Display

Excel VBA to copy and paste columns

More details

Similar Threads

Copy/Paste Excel Range/Chart into Powerpoint VBA

VBA : how copy data cells from workbook to another and paste in columns

Filter And Copy Range From Excel And Paste In PPT Presentation Slides Using Excel VBA

Macro Copy Columns and Paste in rows.

Copy webpage and paste it as HTML into an excel sheet using vba

Bookmarks

Bookmarks

Posting Permissions