formula to auto insert data

[ajb_1976]

Hi, i have a problem whereby i have 2 worksheets and the data that is entered on sheet1 is automatically dropped into sheet2 by means of ='sheet1'!A2..........'sheet1!Z2' etc being entered as the formulae in the relevant cells.
The problem is when rows are inserted into sheet1 the values are not dropped into sheet2 i.e. if on sheet1 a new row in inserted after row 2 it is then row 3, however on sheet2 the reference to row3 is not present it goes from 2 to 4.
Is there anything that can be done to prevent this or any formula that can be used to allow the addition of new rows.
Thanks

 

[HaHoBe]

Hi, ajb_1976,

you either could use VBA to get the job done (if you manually enter the data the Worksheet_Change event behind Sheet1 could trigger this and copy everything to the backup) or make extensive use of the Indirect-Function to really get the value from a certain cell in Sheet1 to Sheet2 regardless how many columns or rows have been inserted or deleted. Please make sure to use an If statement on either an error or an empty value in cells on Sheet1.

Ciao,
Holger

 

[ajb_1976]

Thanks for the reply. I was able to solve the issue using the INDIRECT function as you mentioned. I entered the code and used the fill handle to copy to all required cells
Below is example of code used for any others with similar issues.
Thanks

=INDIRECT("'booking ledger'!$A$"&ROW(2:2))

 

[Pep2535]

Problems Creating Rows With Macros

Hi,

I'M BIULDING A MACRO IN ORGER TO INSERT ONE ROW AFTER THE FIRST EMPTY CELL, COPYING FORMAT FROM PREVIOWS CELL AND DOING THE SAME IN A DIFFERENT SHEET. I'LL SHOW YOU

Sub INSERTAR()
'
' INSERTAR Macro
' Macro grabada el 17/05/2007
'

'
ActiveSheet.Unprotect
Application.Run "'SANTACLAUS.xls'!Final"

WHICH IS THIS MACRO

Sub Final()
Sheets("Hoja1").Select
Range("H2").Select
While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Wend
End Sub

IT GOES BACK TO THE PREVIOUS MACRO

ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.PreviousRow.Select TO THE ONE JUST INSERTED
Selection.Copy
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "Y:\2007"
Workbooks.Open Filename:= _
"Y:\2007\ANNUAL FEE.xls" _
, UpdateLinks:=3
Sheets("HOUSE").Select
ActiveSheet.Unprotect
Application.Run "'ANNUAL FEE.xls'!Final"

SAME AS PREVIOUS MACRO

ActiveCell.Select
Selection.Insert Shift:=xlDown
Rows("809:809").Select SHOULD BE PREVIOUS ROW TO THE ONE JUST INSERTED
Selection.Copy
Rows("810:810").Select SHOULD BE THE INSERTED ROW
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B809:AM809").Select PREVIOUS ROW
ActiveWindow.SmallScroll ToRight:=1
Selection.AutoFill Destination:=Range("B809:AM810"), Type:=xlFillDefault COPY FORMULAS FROM PREVIOUS TO INSERTED
Range("B809:AM810").Select NOT NEEDED
Range("AM810").Select NOT NEEDED
Windows("PLAN DE VISITAS 2007.xls").Activate
Windows("SANTACLAUS.xls").Activate
Range("A653").Select NOT NEEDED
ActiveWindow.ScrollRow = 647
ActiveWindow.ScrollRow = 638
ActiveWindow.ScrollRow = 628
ActiveWindow.ScrollRow = 618
ActiveWindow.ScrollRow = 608
ActiveWindow.ScrollRow = 598
ActiveWindow.ScrollRow = 582
ActiveWindow.ScrollRow = 566
ActiveWindow.ScrollRow = 556
ActiveWindow.ScrollRow = 538
ActiveWindow.ScrollRow = 526
ActiveWindow.ScrollRow = 511
ActiveWindow.ScrollRow = 497
ActiveWindow.ScrollRow = 479
ActiveWindow.ScrollRow = 462
ActiveWindow.ScrollRow = 443
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 391
ActiveWindow.ScrollRow = 362
ActiveWindow.ScrollRow = 339
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 7
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:= _
True
ActiveWindow.Close
Range("H793").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:= _
True
End Sub

I HAVE TWO FILES. ONE I USE TO INSERT DATA AND THE OTHER ONE I USE TO SEE THAT DATA (AND INSERT SOME LINKS) BUT NOT BEING ABLE TO CHANGE IT (PROTECTED) UNLESS YOU ARE A SPECIFIC USER. I HAVE FORMULAS IN ANNUAL FEE SO I GET THE INFORMATION FROM SANTACLAUS.

I HAVE SANTACLAUS.XLS WHERE I INSERT DATA WHICH IS COOPIED TO ANNUAL FEE.XLS SO WHEN I RUN OUT OF ROWS I NEED TO INSERT ONE IN SANTACLUS.XLS ANOTHER ONE IN ANNUAL FEE.XLS AND COPY THE FORMULAS FROM THE PREVIOUS ROW IN ANNUAL FEE SO WHAT EVER YOU INSERT IN SANTACLAUS.XLS IT IS THE SAME AS YOU CAN SEE IN ANNUAL FEE.XLS.

SO I NEED TO BE ABLE TO SELECT FIRST EMPTY CELL SO I INSERT ONE NEW ROW (ON BOTH SHEETS) AND SOMEHOW COPY FORMAT FROM PREVIOUS ROW... I'M SURE THIS IS POSSIBLE, I JUST DON'T KNOW HOW.

ON TOP OF THAT, ON BOTH SHEETS I HAVE CREATED ALLOWED SOME USER TO MODIFY SOME RANGES WITHIN THE SHEET, WHICH MEANS THAT IF I INSERT NEW ROWS, I SHOLD UPDATES THOSE RAGES SO THE USERS CAN ACCES THE SPECIFIC CELLS WITHIN THE RANGE...

THANKS FOR ALL YOUR HELP.