Creating sequential work order numbers

[UMAKEMESIK]

I have sheet 1 with a macro button. I would like to click on that button and it will call to a macro or another sheet within the workbook to create a new number ( last po + 1) number and then paste it into a specified cell on worksheet 1.

any thoughts.

thanks

 

[Alphacsulb]

Do you have a macro assigned to that button already? If so what is it?
What are the names of the other sheets?
How do you determine what the last number is? Is it in a cell?

Sample data would help in your request. I would suggest Excel Jeanie

 

[UMAKEMESIK]

i have this working but it calls to a separate workbook on the network.

here is the macro for that call:

Application.ScreenUpdating = False
    ChDir _
        "\\Fpsea\Groups\CustServ\SAMPLE REQUEST\SAMPLE REQUEST LOG"
    Workbooks.Open Filename:= _
        "\\Fpsea\Groups\CustServ\SAMPLE REQUEST\SAMPLE REQUEST LOG\numberlog.xls"
    Range("A1").Select
    Selection.Copy
    ActiveWorkbook.Close
    Range("AB3").Select
      ActiveSheet.Unprotect "dod"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True
    Application.ScreenUpdating = True

This opens up Numberlog.xls - on that worksheet a new number is created. Then it copies and pastes the number on the first worksheet in cell ab3.

I can't figure out how to do all of this on the same workbook. i.e. my first worksheet is my workorder form. on that form i will have a macro button and when clicked it will call to sheet 2 , generate a new number and then past it into cell ab3 on worksheet 1.

thanks

 

[Alphacsulb]

If I understand you correctly the workflow is as follows:

1) Macro posted opens document
Copies Cell Value of A1 of Numberlog.xls
Paste Value in Worksheet 1 - Sheet 1 - Cell AB3
2) The value of AB3 is suppose to be the last number of the sequence.
3) Generate the numbers in Worksheet 2- Sheet 2.
4) Copy and Paste the values from Sheet 2 into Sheet 1 starting at AB3

Are my assumptions correct?

 

[UMAKEMESIK]

That is correct!!

 

[Alphacsulb]

Let me work on that. My hunch is that we will have to call on 2 macros to accomplish this, but only use 1 button as indicated. Keep in mind there are multiple ways of doing this.

In the future make a habit of creating a workflow so that it's easier to ask and answer questions.

 

[UMAKEMESIK]

Thanks for your time, it is much appreciated.

Will do on the recommendations going forward.

 

[Alphacsulb]

I'm still working on it, I'm finding it difficult to number sequentially based on 1 cell. Can you have a helper column in AC or cell in AC3?

 

[UMAKEMESIK]

Here is some of the code from the number log.


I have this in module 1

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/2/2006 by MartinR
'

'
    Range("A1").Select
    Selection.Copy
    'Range("O1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

I have this in This workbook:

Private Sub Workbook_Open()


With ThisWorkbook.CustomDocumentProperties("LastPO")
      .Value = [Currentpo]
 [Currentpo] = ThisWorkbook.CustomDocumentProperties("LastPO") + 1
   
   
   End With

   'insert your code that clears the worksheet

   'Force a save of the worksheet
   ThisWorkbook.Save
   
   

  
End Sub

Then on the Numberlog workseet. I have File >Properties> custom Tab - Name - last po, type - number - value 3000

Then on cell a1 of the number log i have cell a1 set at 3000 and I call that cell current po.


Not sure how it all works but it does as two seperate workbooks. The numberlog.xls generates the last number +1 evertime it is opened.

Then my sample request calls for that number and paste it into cell ab3. I didn't create this , I had help.

but now I am looking for a way to incorpoerte into one workbook.

thanks again!!!!!!!!

 

[Alphacsulb]

Sub solution()
Application.ScreenUpdating = False
    ChDir _
        "C:\" 
    Workbooks.Open Filename:= _
        "C:\TestValue.xls" 'my test file
    
        Range("A1").Select
    Selection.Copy
    ActiveWorkbook.Close
    Range("B1").Select 'in my example i use B1 instead of ab3
    ActiveSheet.Unprotect "dod"
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True
' finished importing the cell value
' start or numbering in sheet 2
Dim i As Integer
Sheets("Sheet2").Select
Range("C:C").Clear ' where i wanted the sequential number to go
Cells(1, 3).Select
i = 1
ActiveCell.Value = i
Do While i < Worksheets("Sheet1").Range("B1").Value
If ActiveCell.Row = 65535 Then ' if more than this amount then move to next column
Cells(1, ActiveCell.Column + 1).Select
Else
ActiveCell.Offset(1).Select
End If
i = i + 1
ActiveCell.Value = i
Loop
' end of numbering
' start of copy and paste back into sheet 1
Sheets("Sheet1").Select
ActiveSheet.Unprotect
Sheets("Sheet2").Select
Columns("C:C").Select
Selection.Copy
Sheets("Sheet1").Select
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select
Application.ScreenUpdating = True
' end of macro
End Sub

Here is what I ended up with. Make an excel sheet and store in directory C drive named Testvalue.xls with the cell A1 being the end of the sequential number you want. Save and close.

Put this macro in a test worksheet to see what it does. I believe that it does everything we discussed.

If it does then all you should have to do is put your original path back in and change cell to ab3.