Copy & Pasting in a Dynamic Range

[EVANWIT84]

Hi All,

This is my 1st post!

I'm trying to take the date from sheet8 and past it into 68 for a dynamic range. The code that I have written will paste the date into the last row of the range exactly where I want it. However, I want to copy that date and paste it into the beginning of the range that I'm working with. The range begins @ cell 491 right now but will shift next month and so on. My first though was to Dim FirstRow as Range("I1").End(xlDown) but that isn't working or copy active cell and xlup again but that doesn't work either.

Dim s As Worksheet
Dim t As Worksheet

Set s = Sheet8
Set t = Sheet68

Sheet8.Select
Range("B2").Value = Right(Range("A6").Value, 11)
Range("b2").Copy t.Range("H1048576").End(xlUp).Offset(0, 1)

Lastly, I'm not looking for someone to solve this for me but a point in the right direction would be helpful.

 

[davesexcel]

The assumption is that row 1 will be static.
We will find the last column in row 1 then find the last row in the last column.

Sub LstRwInLstCol()

    Dim s As Worksheet
    Dim t As Worksheet
    Dim rw As Long, c As Long, rng As Range
    
    Set s = Sheet8
    Set t = Sheet68

    With t
        c = .Cells(1, .Columns.Count).End(xlToLeft).Column
        rw = .Cells(.Rows.Count, c).End(xlUp).Row
        Set rng = .Cells(rw + 1, c)
    End With
    
    rng = Right(s.Range("A6").Value, 11)

End Sub

 

[EVANWIT84]

Magic! Thanks for your help Dave.