Sequential Numbering

R

Rob_Sobey

New Member
Joined
May 30, 2012
Messages
12
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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"
Workbooks.Add
ActiveWorkbook.SaveAs n
ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
ActiveWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
End Sub

Probably a lot of better ways, though.
 
Upvote 0
Better yet even a separate sheet with the sequential numbers and use the sheet activate event instead.
 
Upvote 0
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?
 
Upvote 0
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"
Workbooks.Add
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
 
Upvote 0
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.
 
Upvote 0
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"
Workbooks.Add
ActiveWorkbook.SaveAs n
ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
ActiveWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Sheet2").Range("A1").Delete shift:=xlUp

End Sub
 
Upvote 0
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.
 
Upvote 0
OK, lets try again:

Code: 
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("Sheet2").Range("A1").Delete shift:=xlUp
wb.Activate
wb.Sheets("Sheet1").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Deleting Form Control in copied destination workbook
Replies
0
Views
174
CMKNIGHT
C
S
  • Question
Run continuous macro with input file attached to it
Replies
1
Views
240
shansakhi
S
X
  • Question
Formula bar displaying SUM formula, but not its arguments
Replies
4
Views
282
KRice
K
D
  • Solved
Range in worksheet1 Insert into worksheet2 from row 2
Replies
2
Views
173
dufusgoofus
D
M
  • Question
SaveAs CSV on Mac
Replies
7
Views
652
MARK858
MARK858

Forum statistics

Threads
1,215,137
Messages
6,123,252
Members
449,093
Latest member
Vincent Khandagale

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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