PDA

View Full Version : PROBLEMS CREATING ROWS WITH MACROS



Pep2535
18-05-2007, 05:04 AM
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.