INSERTING WORKSHEETS
Posted by JUDY on April 24, 2001 12:18 PM
IS THERE A PLACE I CAN SET WHAT I WANT THE NEXT WORKSHEET NUMBER TO BE. I DON'T WANT TO DO IT MANUALLY BECAUSE I AM USING A MACRO. IF A WORKSHEET IS DELETED THE NEXT ONE INSERTED GOES TO THE NEXT NUMBER. I WOULD LIKE TO BE ABLE TO RESET THAT NUMBER.
Posted by Jerid on April 24, 2001 12:47 PM
Hi Judy
One way you can solve this is to rename the new sheet as soon as it gets added in your Macro.
'Find a line similar to this
Sheets.Add
'and add a line similar to this
ActiveSheet.Name = "sheet999"
Jerid
Posted by JUDY on April 24, 2001 1:00 PM
Thanks!!!!!!!! that will work. I am also pasting two different pieces of info to the same worksheets. Do you know how to make the second items I am pasting find the next available line. Right now I am just having it paste to line 1000 then manually deleteing lines.
Posted by Jerid on April 24, 2001 1:08 PM
I don't think I understand 100%. Paste that piece of your code into a message.
Posted by Anon on April 25, 2001 6:42 AM
There are many ways to do this - it depends upon what "next available line" means. For example, if you want to select the next row after the worksheet's "used range" :-
Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).EntireRow.Select
Posted by Judy on April 25, 2001 12:14 PM
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/24/2001 by General Data Company
'
'
Selection.AutoFilter Field:=5, Criteria1:="OH"
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3:J5000").Select
Selection.Copy
Sheets.Add
ActiveSheetName = "Sheet2"
Range("A3").Select
ActiveSheet.Paste
Range("A3").Select
Selection.End(xlDown).Select
Range("A1000").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=5, Criteria1:="<>OH", Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:="OH"
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3:J5000").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1000").Select
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Range("A2").Select
Right now where is says range 1000 that is where I am leaving the spreadsheet so when it comes back the paste will be there. Also I am getting a error with the add sheet name. Can you see what I did wrong. I really appreciate your help.
Posted by Jerid on April 26, 2001 9:28 AM
Well,
I see that the line ActiveSheetName = "Sheet2" should be Application.Activesheet.Name = "sheet2", it looks like you forgot the dot.
It might just be a habit of mine but I always user Application. (that's Application dot) in front of things like Sheets and Range and a few others because I have seen weird errors without it.
As far as finding the last cell, it looks like you are already doing it with Selection.End(xlDown).Select, I would just add Activecell.offset(1,0).Select to move to the next row.
I hope this helps.
