Update data fill down dynamically

[oska]

Hi,

I have a worksheet (in Excel 2010) which I update weekly and that I want automate the update process with a macro. Basically I fill down the latest data which is on a weekly basis by adding a new row (all cells contain a formula or are blank). I have done this manually by using keyboard shortcuts and recorded a macro as I've done it. Here is the macro:

Sub filldown_sheet()
'
' filldown_sheet Macro
' fill down/update datastream data
'
'
Range("B7").Select
Selection.End(xlDown).Select
Range("A1102").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A1102:P1103").Select
Selection.FillDown
End Sub

This works fine except the range doesn't automatically/dynamically update. how can I do this so it automatically changes to select the most recent data i.e. dynamically update?

many thanks

[YasserKhalil]

As for your macro . Why all these lines of selection?
I Think your code should be like that to do the task

Range("A1102:P1103").Select
Range("A1102:P1103").FillDown


As for your data .. What is the whole range of your data (for example :A7:B1102)? what's the column which you would like to get the last row .. as the last row variable will determine the last row to make your range dynamic in code?

Generally try this may be fulfill your task

Sub FillDownSeet()
Dim LR As Long, LC As Long, Rng As Range
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(LR, Columns.Count).End(xlToLeft).Column
Set Rng = Range(Cells(LR, 1), Cells(LR + 1, LC))
Rng.Select
Rng.FillDown
End Sub

[snb]

Wouldn't this be sufficient ?


Sub M_snb()
Range("A1102:P1103").FillDown
End Sub

[oska]

As for your macro . Why all these lines of selection?
I Think your code should be like that to do the task

Range("A1102:P1103").Select
Range("A1102:P1103").FillDown


As for your data .. What is the whole range of your data (for example :A7:B1102)? what's the column which you would like to get the last row .. as the last row variable will determine the last row to make your range dynamic in code?

Generally try this may be fulfill your task

Sub FillDownSeet()
Dim LR As Long, LC As Long, Rng As Range
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(LR, Columns.Count).End(xlToLeft).Column
Set Rng = Range(Cells(LR, 1), Cells(LR + 1, LC))
Rng.Select
Rng.FillDown
End Sub

Thanks YasserKhalil. This code works a treat.

by the way, how do I get this code to work automatically across multiple worksheets?