VBA copying date from specific cell down to bottom of list

[Jed Shields]

Morning al,

I need a bit of help with this, it's falling over on the last line. Basically, I'm copying all the data from All Data Current sheet to the bottom of All Data History Sheet This works fine. The History sheet has one more column where I tag the date (1/8/18, 1/9/18 etc.). The code manages to copy and paste the date from Macro E18 to the botom of the date column where there's the date for the last months data, but the final line that nees to copy it down to the bottom of the list doesn't work. Any ideas? Apologies, the code isn't pretty and could probably be done more efficiently...




Dim LRowC As Variant
Dim LRowH As Variant
Dim LRowH2 As Variant
Dim DCell As Variant


Sheets("All Data History").Select
With ActiveSheet
LRowH = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Sheets("All Data Current").Select
With ActiveSheet
LRowC = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ActiveSheet.Range("$A$2:$X" & LRowC).SpecialCells _
(xlCellTypeVisible).Copy
Sheets("All Data History").Range("A" & LRowH + 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False 'Doesn't add data to clipboard


Sheets("All Data History").Select
With ActiveSheet
LRowH2 = .Cells(.Rows.Count, "A").End(xlUp).Row
' DCell = .Range(.Rows.Count, "Y" & LRowH + 1).Cell
End With
Range("Y" & LRowH + 1).Select
ActiveCell.Formula = "=Macros!$E$16"
Range("Y" & LRowH + 2).AutoFill Destination:=Range(DCell & ":y" & LRowH2)

End Sub[/CODE]

 

[AlphaFrog]

Re: VBA help reqd, copying date from specific cell down to bottom of list

Try something like this...

    [COLOR=darkblue]Dim[/COLOR] NextRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("All Data Current")
        .Range("A2:X" & .Cells(.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("All Data History")
        NextRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
        .Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]    [COLOR=green]'Doesn't add data to clipboard[/COLOR]
        .Range(.Range("Y" & NextRow), .Range("Y" & .Range("A" & Rows.Count).End(xlUp).Row)).Value = _
            Sheets("Macros").Range("E16").Value
        .Select
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

 

[Jed Shields]

Re: VBA help reqd, copying date from specific cell down to bottom of list

Works perfectly, many thanks :D