Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update a sheet through Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update a sheet through Macro
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...d.php?t=123316 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update a sheet through Macro
the code below will add the rows add, clear the empty cells, and leave the
formulas "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...d.php?t=123316 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update a sheet through Macro
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 "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...d.php?t=123316 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update a sheet through Macro
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 ). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Update a sheet through Macro
'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 ). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto update on seperate sheet | Excel Worksheet Functions | |||
Summary sheet auto update | Excel Worksheet Functions | |||
I need a payroll sheet that can auto update figures each week | Excel Worksheet Functions | |||
Auto Update Sheet Tab | Excel Worksheet Functions | |||
auto update an open excel sheet over network, with mutiple users | Excel Worksheet Functions |