Find Last Used Cell in Column and Autofill Data Down to Last Cell in Adjacent Column

[mclark13]

Hello, I have been working on trying to identify the solution to this for the past day with little success. Any assistance or guidance would be greatly appreciated.

I am copying data from one sheet (sheet A) and pasting into another sheet (sheet B). I can find the cell I need to paste the information into in Sheet B and am able to paste that information just fine. Once I paste the data to Column B in Sheet A, I find the last value in Column A and offset by 1 to go to the first empty cell as you can see in the code. At which time I add Today's date. What I am struggling with is autofilling the formula down to the last used cell in the adjacent column. I have found other posts that describe how to autofill down in a specific range (A1:A500 for example), but I am struggling with how to do it with a dynamic range.

Sub QBFindLastRow()
'Paste data into last open cell in column "B" for OrderID
'Add today's date to the next available empty cell in Column A and autofill down to last used cell in Column B
'Copy all the cells that were autofilled down and paste back as a value because the date needs to remain static since this is done each day

    Windows("1_b_MasterAllProductsUPCPID_Oracle_PC.xlsm").Activate
    Range("B1048576").End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    
    Range("A1048576").End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    ActiveCell.copy
   
End Sub

Thanks in advance for any ideas.

 

[MARK858]

The code below will autofill from B1 to the cell in Column B that is the same as the last cell in Column A

Sub CopyDown()
Range("B1").AutoFill Destination:=Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

 

[mclark13]

Thank you Mark858 for such a quick reply. I may be missing it, but this is what I am trying to do. Each day I am adding to the list of data so the last used Cell in Column A will be dynamic as will the last used cell in Column B.

    Date           OrderID
1  07/16/2013      A12345
2  07/16/2013      B12345
3  07/16/2013      C12345
4  07/17/2013      D12345
5  Autofill        E12345
6  Autofill        F12345
7  Autofill        G12345
8  Autofill        H12345
9
10

Thank you again Mark858, much appreciated.

 

[MARK858]

Try

Sub CopyDown()
    Dim LRowB As Long, LRowA As Long
    With Sheets("SheetA")
        LRowB = .Range("B" & Rows.Count).End(xlUp).Row
        LRowA = .Range("A" & Rows.Count).End(xlUp).Row
        .Cells(LRowA, 1).AutoFill Destination:=.Range(.Cells(LRowA, 1), .Cells(LRowB, 1))
    End With
End Sub

 

[mclark13]

Mark858, thank you it works just like I need it to. Thank you again! As always, now I will try to understand what it is doing. Much appreciated Mark858. Have a wonderful day.

 

[MARK858]

Your welcome