Forum Home
Press F1
Thread ID: 79372	2007-05-17 17:04:00	PROBLEMS CREATING ROWS WITH MACROS	Pep2535 (12278)	Press F1

Post ID	Timestamp	Content	User
550704	2007-05-17 17:04:00	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.	Pep2535 (12278)
1