Copy column to Another sheet...incrementally increasing the column every run of the vba

[chiswickbridge]

Hi,

I have dynamic data on one sheet in a Column E2 to E101. This is updated on a daily basis.

Need a VBA to copy this column and save this data for future use, on a different sheet, Starting Column is AA.

AA1 will contain the date and AA2 to AA101 will have the data correspondingly.

Next day the saved data will use column AB and so forth...

Thank you in advance...

 

[mumps]

Try:

Sub CopyCol()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lCol As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    If lCol < 27 Then
        Range("AA1") = Date
        Range("E2:E" & LastRow).Copy Range("AA2")
    ElseIf lCol >= 27 Then
        lCol = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
        Cells(1, lCol) = Date
        Range("E2:E" & LastRow).Copy Cells(2, lCol)
    End If
    Application.ScreenUpdating = True
End Sub

 

[chiswickbridge]

Hi...I need to copy from sheet named e.g "Name1" to sheet named 'Name2"

 

[mumps]

Change the sheet names (in red) to suit your needs.

Sub CopyCol()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Name1")
    setr desWS = Sheets("Name2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        If lCol < 27 Then
            .Range("AA1") = Date
            Range("E2:E" & LastRow).Copy .Range("AA2")
        ElseIf lCol >= 27 Then
            lCol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
            .Cells(1, lCol) = Date
            Range("E2:E" & LastRow).Copy .Cells(2, lCol)
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[chiswickbridge]

Thank You....

 

[mumps]

You are very welcome.

 

[abdelfattah]

hi, mumps it's very strange in post#4 if the code works it supposes showing error it should set not setr in this line

    setr desWS = Sheets("Name2")

and i would ask you about this line

LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

what look for in last row i always see * what means and when use it this

 

[mumps]

@abdelfattah
You are correct. "setr" should be "Set". That was a typo on my part.

The asterisk (*) is a wild card to find any data that is contained in the last used row. You use this line of code to return the row number of the last used row.

 

[abdelfattah]

thanks mumps for this explanation

 

[chiswickbridge]

YESSSSS...Works fine...You made my week-end....I have planned to have a a small BBQ tomorrow...Will raise my glass to u...