Sequential Numbering

[Rob_Sobey]

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"
Workbooks.Add
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

 

[John Davis]

You're welcome. Glad it worked for you, and thanks for the feedback.

 

[Rob_Sobey]

John, there is another question i think you would be able to answer and my browser is not functioning properly so I can't actually open a new thread so thought to ask you this:

Taking the macro you worked out for me, now how do I do the pasting of the cells into the new workbook, but with their formatting. I have no conditional formatting in the initial book, but I do have some cells filled with yellow and I also have some text bold in some cells. This presently doesn't copy across like that.

I have tried changing the PasteSpecial xlPasteValuesAndNumberFormats to other options, but it just errors.

Reckon you could help?

 

[John Davis]

Perhaps:

Sub RobSobey()
Dim n As String
Dim wb As Workbook
Dim wbn As Workbook

Application.DisplayAlerts = False
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"
Set wbn = Workbooks.Add
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("Sheet1").UsedRange.Copy
wbn.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats
wb.Sheets("Sheet2").Range("A1").Delete shift:=xlUp
wb.Activate
wb.Sheets("Sheet1").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Untested try on a copy first.