# Sequential Numbering

#### Rob_Sobey

##### New Member
I have developed a workbook that has data with various VLOOKUP's etc. I then have developed a simple Macro that copies the page, openes a new workbook, names the workbook by cell C5 in the original workbook.
For your interest the macro is this:

Sub Res()
Dim n As String
n = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C5").Value & ".xlsx"
ActiveWorkbook.SaveAs n
ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
ActiveWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
End Sub

This works well, but now what I would like to do is have a macro that I can run just prior to running the above mentioned macro, that will put a 5 digit (say 10000) into cell C5 of the worksheet. The next time I open the original workbook I need to see the old number (ie. 10000) I then do a whole lot of work on the worksheet and I need then to run the macro to put in the next sequential number (eg 10001), before running the macro which develops the new workbook.

Basically a sequential number question.

Any suggestions?

Thanks

Rob

Edit: Joe4 logged in as Rob_Sobey and posted (copied this question from his post in About This Board

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
May be a bit Rube Goldbergish:

Create a workbook with the sequential numbers in Column A of Sheet1

Code:
``````Private Sub Workbook_Open()
Sheets("Sheet1").Range("A1").Delete
End Sub``````

then modify your code as follows:

Code:
``````Sub Res()
Dim n As String
Dim wb As Workbook

Set wb = ThisWorkbook

Workbooks.Open Filename:="THIS COULD BE A WORKBOOK WITH SEQUENTIAL NUMBERS"
wb.Sheets("Sheet1").Range("C5").Value = Sheets("Sheet1").Range("A1").Value
ActiveWorkbook.Close True

n = wb.Path & "\" & Sheets("Sheet1").Range("C5").Value & ".xlsx"
ActiveWorkbook.SaveAs n
ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
ActiveWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
End Sub``````

Probably a lot of better ways, though.

Better yet even a separate sheet with the sequential numbers and use the sheet activate event instead.

Sorry I am a little new to Macros.

I have made a column of sequential numbers from A1 down to A3500 od worksheet 2 of the same workbook named TESTWORKBOOK.xlsx

How would it look now?

Maybe:

Code:
``````Sub RobSobey()
Dim x As String
Dim n As String

x = Sheets("Sheet2").Range("A1").Value
n = ThisWorkbook.Path & "\" & x & Sheets("Sheet1").Range("C5").Value & ".xlsx"
ActiveWorkbook.SaveAs n
ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
ActiveWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Sheet2").Range("A1").Delete shift:=xlUp

End Sub``````

Note: would work up to 3500

John, something seems to go wrong when I run this.
Cell C5 does not update with the new sequential number and also the new workbook is saved by the first number in my list (ie. Cell A1 of Sheet 2 which is 10000) as well as what is entered into C5 on Sheet.

I can send you the workbook if you like.

I'm sorry. I wasn't sure about what you needed, try this.

Code:
``````Sub RobSobey()
Dim n As String

Sheets("Sheet1").Range("C5").vALUE = Sheets("Sheet2").Range("A1").Value
n = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C5").Value & ".xlsx"
ActiveWorkbook.SaveAs n
ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
ActiveWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Sheet2").Range("A1").Delete shift:=xlUp

End Sub``````

Hello John

It still is not naming C5 in the TESTWORKBOOK, Sheet 1 by the next sequential number in column A of Sheet 2 of the same workbook.
In addition to that, it is naming the newly produced workbook by the previous number and as a result it is erroring at the

ActiveWorkbook.SaveAs n

because there is another workbook by that name in the same folder.

Sorry John, I'm struggling on this one.

OK, lets try again:

Code:
``````Sub RobSobey()
Dim n As String
Dim wb As Workbook
Dim wbn As Workbook

Application.ScreenUpdating = False

Set wb = ActiveWorkbook

wb.Sheets("Sheet2").Range("A1").Copy wb.Sheets("Sheet1").Range("C5")
n = wb.Path & "\" & wb.Sheets("Sheet1").Range("C5").Value & ".xlsx"
On Error Resume Next
wbn.SaveAs n
On Error GoTo 0
wb.Sheets("Sheet1").UsedRange.Copy
wbn.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
wb.Sheets("Sheet2").Range("A1").Delete shift:=xlUp
wb.Activate
wb.Sheets("Sheet1").Select

Application.ScreenUpdating = True

End Sub``````

Last edited:
Thanks very much John, it works like a charm.

Replies
0
Views
185
Replies
0
Views
584
Replies
0
Views
225
Replies
0
Views
309
Replies
10
Views
982

1,203,431
Messages
6,055,338
Members
444,781
Latest member
rishivar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back