'put this code in the sheet2 VBA page so when the sheet is selected the
formulas get updated
Private Sub Worksheet_Activate()
Sh2LastRow = Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet1")
Sh1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End With
If Sh1LastRow Sh2LastRow Then
Rows(Sh2LastRow).Copy _
Destination:=Rows(Sh2LastRow & ":" & Sh1LastRow)
End If
End Sub
"Chetan" wrote:
On Aug 7, 1:14 pm, Simon Lloyd
wrote:
What would be the ranges used? where will the entries be? all in the
same column?, where are the formulae that you want extending, sheet 1 or
sheet 2?
I don't understand your 2 points in point 1 you say "sheet2 should copy
the formula in the last row.....etc but in point 2 you say you want the
same formulae pasting to sheet1? can you clarify what it is you are
trying to achieve?
Chetu;444768 Wrote:
Hi,
I have Sheet 1 where the user enters the data of a particulra product,
Sheet 2 is linked to Sheet 1 with lot of formulas ( basically for
analysis). I link the Sheet 2 with sheet 1 through formulas. I dont
want to copy the formulas to the entire Sheet 2 since that will
increase the size.
Can anyone suggest me a Macro , Below is the steps for your
understanding
1) When user enters 10 new entries, the sheet 2 should copy the
formulas in the last row and extend the same to 10 rows ( this will
update the new data)
2) The macro shoudl apply the formula by copying the last row in sheet
2 and then paste the formulas only to the number of new updates in
Sheet 1.
Regards
Chetan J
--
Simon Lloyd
Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/sh....php?t=123316- Hide quoted text -
- Show quoted text -
Hi Lloyd,
Sorry for the confusion,
Actually Sheet 1 is used by the user, But Sheet 2 is linked to Sheet
1, So Sheet 2 has formulas starting range from B12 to DN12 ( like
refering sheet1 and also some other formulas for analysis), I want a
macro which copies the B12 to DN12 row and pastes them in B13 to DN13,
once the formulas are copied, the formulas will automatically fetch
the data from Sheet 1.
But the problem is, If a user enters data in 10 new ros in Sheet1, The
same should reflect in Sheet2 ( I know i can copy paste the formulas,
but this method increases the file size ).