Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autonumber plus
I am keeping track of invoice revisions. .Each new worksheet is copied into
a new workbook. Worksheets are grouped by worksheet number and renamed as by invoice number using the following code.: Public Sub RenameSheet() NewName = Range("c7").Value & "-" & Range("h4").Value ActiveSheet.Name = NewName End Sub Currently I have users enter a sequential value in h4. Range c7 contains the invoice number, which must remain the same. I would like to automate this part. So that it first looks to c7, copies it, then addes a sequential number in h4, avoiding duplication and starting with 1. any help would be most appreciated -- stan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
autonumber plus
Humm, you may find a use for this:
Sub InsertMissingNum() Range("A2").Select Do Until ActiveCell.Value = Empty If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 Then ActiveCell.Offset(1, 0).Select Else ActiveCell.EntireRow.Insert ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 ActiveCell.Offset(1, 0).Select End If Loop End Sub It increments by 1, and inserts rows so your existing data stays the same, relative to your new data. So, let's say you have 1, 2, 3...8...10, all in Column A. Run the macro. Does it do what you want? HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stan" wrote: I am keeping track of invoice revisions. .Each new worksheet is copied into a new workbook. Worksheets are grouped by worksheet number and renamed as by invoice number using the following code.: Public Sub RenameSheet() NewName = Range("c7").Value & "-" & Range("h4").Value ActiveSheet.Name = NewName End Sub Currently I have users enter a sequential value in h4. Range c7 contains the invoice number, which must remain the same. I would like to automate this part. So that it first looks to c7, copies it, then addes a sequential number in h4, avoiding duplication and starting with 1. any help would be most appreciated -- stan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
autonumber plus
it solved the problem. thanks
-- stan "ryguy7272" wrote: Humm, you may find a use for this: Sub InsertMissingNum() Range("A2").Select Do Until ActiveCell.Value = Empty If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 Then ActiveCell.Offset(1, 0).Select Else ActiveCell.EntireRow.Insert ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 ActiveCell.Offset(1, 0).Select End If Loop End Sub It increments by 1, and inserts rows so your existing data stays the same, relative to your new data. So, let's say you have 1, 2, 3...8...10, all in Column A. Run the macro. Does it do what you want? HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stan" wrote: I am keeping track of invoice revisions. .Each new worksheet is copied into a new workbook. Worksheets are grouped by worksheet number and renamed as by invoice number using the following code.: Public Sub RenameSheet() NewName = Range("c7").Value & "-" & Range("h4").Value ActiveSheet.Name = NewName End Sub Currently I have users enter a sequential value in h4. Range c7 contains the invoice number, which must remain the same. I would like to automate this part. So that it first looks to c7, copies it, then addes a sequential number in h4, avoiding duplication and starting with 1. any help would be most appreciated -- stan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoNumber | Excel Programming | |||
AutoNumber/RecordID | Excel Programming | |||
Excel Autonumber? | Excel Programming | |||
autonumber | Excel Discussion (Misc queries) | |||
Question on autonumber | Excel Programming |