Auto Update a sheet through Macro

[Chetu]

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

 

[Joel]

the code below will add the rows add, clear the empty cells, and leave the
formulas

 

[Joel]

The last code I gave put the new rows in front of the last row. this code
puts them after the last row. I usually like keeps the last row.

Sub AddRows()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Count = 1 To 10
Rows(LastRow).Copy
Rows(LastRow).Insert
Next Count
LastCol = Cells(LastRow, Columns.Count) _
.End(xlToLeft).Column
For Colcount = 1 To LastCol
'if not a formula clear the cell
If Left(Cells(LastRow + 1, Colcount).Formula, 1) <> "=" Then
Range(Cells(LastRow + 1, Colcount), _
Cells(LastRow + 10, Colcount)).ClearContents
End If
Next Colcount

End Sub

 

[Chetan]

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:











--
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/showthread.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 ).

 

[Joel]

'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