autonumber plus

[stan]

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

 

[ryguy7272]

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---

 

[stan]

it solved the problem. thanks
--
stan

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---