Moving to and from lines within a Script (goto line)

Hi

I was hoping someone could help me on how to move within lines of code. What I mean is I have a script that is running pretty well, however I need part of my script to repeat again just not from the very beginning? Here is an example however I'm still working on this project so bare with my garbage.

I would like to return to this line of code below from the bottom of my script?

Another thing I was thinking is I will need to add a worksheet named info but only if a sheet info doesn't exist not sure how to write that bit of code either.

Still Learning and Appreciate all your help...

Thank You,

Mike

Sub Macro100()
    
    
    Dim ws As Worksheet, strSource As String
    Dim pt As PivotTable, pc As PivotCache, pf As PivotField
    
    
    For Each ws In ActiveWorkbook.Worksheets
        strSource = ws.Name & "!R4C1:R5000C6"
        ActiveWorkbook.Worksheets.Add      'new worksheet becomes the active sheet
        'ActiveSheet.Name = "Info"
        
        Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, strSource)
        Set pt = pc.CreatePivotTable(Range("A1"))   'pivot table at A1 on new sheet
        
        Set pf = pt.PivotFields("Order Number")

        pt.AddDataField pf, "Count of Order Number", xlCount
        pt.AddFields RowFields:="Name"
    Next ws

    Dim stname As String    Dim sht
    Dim OutR As Long
    Dim Data As String
       
    Data = "Data"
    stname = "Info"
    Worksheets.Add().Name = Data
    
    Sheets(2).Select
    Sheets(2).Name = stname




'stname.Name

'If Not CBool(Len(Sheets(stname).Name)) Then
  '  Worksheet.Add().Name = stname




   For Each cell In Worksheets("Info").Range("B3", Range("B3").End(xlDown))
   cell.ShowDetail = True
   Rows("1:3").Select
   Selection.Insert Shift:=xlDown
   Range("A1").FormulaR1C1 = "=R[4]C"
   Range("C1").FormulaR1C1 = "=COUNT(R[4]C:R[499]C)"
   Range("D1:F1").FormulaR1C1 = "=SUM(R[4]C:R[499]C)"
   Range("A1:F1").Select
   Selection.Copy
   Sheets("Data").Select
   Range("A3").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Selection.Font.Bold = False
   Rows("3:3").Select
   Selection.Insert Shift:=xlDown
   Sheets(2).Select
   Application.DisplayAlerts = False
   Sheets(2).Delete
   Application.DisplayAlerts = True
   Next cell
   Sheets("info").Select
   Application.DisplayAlerts = False
   Sheets("info").Delete
   Application.DisplayAlerts = True
   'Sheets("Jan2").Select
   'Sheets("Jan2:Jan28").Delete
   'Sheets("Data").Select
   Worksheets("Data").Move After:=Worksheets(Worksheets.Count)
   Sheets("Data").Select
   Dim jan As String
   jan = "Jan 2008"
   Sheets("Data").Name = jan
   Sheets(1).Select
   
   Dim Wsht As Worksheet
   
   For Each Wsht In Worksheets
   Select Case True
   Case Wsht.Name Like "Sheet*"
  'Case Wsht.Name Like "Info*"
  '   Application.DisplayAlerts = False
  '     Wsht.Delete
  '  Application.DisplayAlerts = True
    End Select
Next Wsht

   
   

   
   
   
   
   
   
   
   
   
   
   
   
   
    'Dim Wsht As Worksheet
    
    'Sheets(2).Select
    'Sheets(2).Delete
    
    
    

    'For Each Wsht In Worksheets
    'Select Case True
    'Case Wsht.Name Like "Sheet*"
  '  Case Wsht.Name Like "Info*"
     '   Application.DisplayAlerts = False
       '     Wsht.Delete
      '  Application.DisplayAlerts = True
    'End Select
'Next Wsht

   'Next cell
    
    
'Sheets(stName).ClearContents
'OutR = 3
'For Each sht In Sheets
 '   If sht.Name <> stName Then
  '      sht.Range("A1:E1").Copy
    '    Sheets(stName).Cells(OutR, 1).PasteSpecial Paste:=xlPasteAll
    '    OutR = OutR + 1
   ' End If
'Next

'Application.DisplayAlerts = False
'For Each sht In Sheets   'Remove all sheets except "Data"
 '   If sht.Name <> stname Then
  '  Sheets(sht.Name).Delete
    'End If
'Next
'Application.DisplayAlerts = True
        
End Sub

Originally Posted by realniceguy5000

Hi

I was hoping someone could help me on how to move within lines of code. What I mean is I have a script that is running pretty well, however I need part of my script to repeat again just not from the very beginning? Here is an example however I'm still working on this project so bare with my garbage.

I would like to return to this line of code below from the bottom of my script?

Another thing I was thinking is I will need to add a worksheet named info but only if a sheet info doesn't exist not sure how to write that bit of code either.

Still Learning and Appreciate all your help...

Thank You,

Mike

Sub Macro100()
    
    
    Dim ws As Worksheet, strSource As String
    Dim pt As PivotTable, pc As PivotCache, pf As PivotField
    
    
    For Each ws In ActiveWorkbook.Worksheets
        strSource = ws.Name & "!R4C1:R5000C6"
        ActiveWorkbook.Worksheets.Add      'new worksheet becomes the active sheet
        'ActiveSheet.Name = "Info"
        
        Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, strSource)
        Set pt = pc.CreatePivotTable(Range("A1"))   'pivot table at A1 on new sheet
        
        Set pf = pt.PivotFields("Order Number")

        pt.AddDataField pf, "Count of Order Number", xlCount
        pt.AddFields RowFields:="Name"
    Next ws

    Dim stname As String    Dim sht
    Dim OutR As Long
    Dim Data As String
       
    Data = "Data"
    stname = "Info"
    Worksheets.Add().Name = Data
    
    Sheets(2).Select
    Sheets(2).Name = stname




'stname.Name

'If Not CBool(Len(Sheets(stname).Name)) Then
  '  Worksheet.Add().Name = stname




   For Each cell In Worksheets("Info").Range("B3", Range("B3").End(xlDown))
   cell.ShowDetail = True
   Rows("1:3").Select
   Selection.Insert Shift:=xlDown
   Range("A1").FormulaR1C1 = "=R[4]C"
   Range("C1").FormulaR1C1 = "=COUNT(R[4]C:R[499]C)"
   Range("D1:F1").FormulaR1C1 = "=SUM(R[4]C:R[499]C)"
   Range("A1:F1").Select
   Selection.Copy
   Sheets("Data").Select
   Range("A3").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Selection.Font.Bold = False
   Rows("3:3").Select
   Selection.Insert Shift:=xlDown
   Sheets(2).Select
   Application.DisplayAlerts = False
   Sheets(2).Delete
   Application.DisplayAlerts = True
   Next cell
   Sheets("info").Select
   Application.DisplayAlerts = False
   Sheets("info").Delete
   Application.DisplayAlerts = True
   'Sheets("Jan2").Select
   'Sheets("Jan2:Jan28").Delete
   'Sheets("Data").Select
   Worksheets("Data").Move After:=Worksheets(Worksheets.Count)
   Sheets("Data").Select
   Dim jan As String
   jan = "Jan 2008"
   Sheets("Data").Name = jan
   Sheets(1).Select
   
   Dim Wsht As Worksheet
   
   For Each Wsht In Worksheets
   Select Case True
   Case Wsht.Name Like "Sheet*"
  'Case Wsht.Name Like "Info*"
  '   Application.DisplayAlerts = False
  '     Wsht.Delete
  '  Application.DisplayAlerts = True
    End Select
Next Wsht

   
 Hi,
   
   
    'Dim Wsht As Worksheet
    
    'Sheets(2).Select
    'Sheets(2).Delete
    
    
    

    'For Each Wsht In Worksheets
    'Select Case True
    'Case Wsht.Name Like "Sheet*"
  '  Case Wsht.Name Like "Info*"
     '   Application.DisplayAlerts = False
       '     Wsht.Delete
      '  Application.DisplayAlerts = True
    'End Select
'Next Wsht

   'Next cell
    
    
'Sheets(stName).ClearContents
'OutR = 3
'For Each sht In Sheets
 '   If sht.Name <> stName Then
  '      sht.Range("A1:E1").Copy
    '    Sheets(stName).Cells(OutR, 1).PasteSpecial Paste:=xlPasteAll
    '    OutR = OutR + 1
   ' End If
'Next

'Application.DisplayAlerts = False
'For Each sht In Sheets   'Remove all sheets except "Data"
 '   If sht.Name <> stname Then
  '  Sheets(sht.Name).Delete
    'End If
'Next
'Application.DisplayAlerts = True
        
End Sub

It's not good programming practice to jump around within a procedure like you propose. It tends to lead to spaghetti code and it's far harder to debug or read.

If there's a section of code you need to use more than once, then you should create a separate procedure for it, and call it from the appropriate place in your main routine

So for instance, copy the whole of the remaining section of code that starts with where you highlighted it in red, and put it in another procedure - say

Sub My_Fantastic_SubRoutine()

End Sub

Now you can simply call the code twice by adding two lines as the last two lines of your Sub Macro100() i.e.

Call My_Fantastic_SubRoutine()
Call My_Fantastic_SubRoutine()

Now the only knock on effect is the declaration of variables. The scope and lifetime of variables is a mini subject in its own right, but in essence variables declared in a procedure with a Dim statement only last to the end of the procedure. If you want to use a variable across procedures you need to declare it in the module's General Section and outside any procedure. So in this case instead of Dim... in the procedure, go to the top of the module and use

Public OutR As Long
Public Data As String
etc.

Strictly speaking you could use Dim, Private or Global but rather than confuse matters just use Public in place of Dim.

Incidentally it's fairly common practice if you're going to declare local variables, i.e. Dim in a procedure, to place them all at the start.

Note that you can also pass undeclared variables between procedures by including them inside the ( ) at the end of the procedure name, but the method just suggested is probably sufficient for this routine at the moment.

To add a sheet called info if it doesn't already exist, use something like:

Sub AddInfoSheet()
Dim x As Integer, bInfoExists As Boolean

For x = 1 To ActiveWorkbook.Worksheets.Count
    If Worksheets(x).Name = "Info" Then bInfoExists = True
Next x
If bInfoExists = False Then Worksheets.Add.Name = "Info"
End Sub

HTH

Wow...That is an answer, Thank You for the pointers much appreciated. I thought my script was a hodge podge creation. But being new I thought better to work on a piece at a time then try to condense some later.I just keep running into road blocks trying to add code after code to this script.

Still Learning...

Anyway Thank You for your time and help much appreciated!!!!

Mike

Originally Posted by realniceguy5000

Wow...That is an answer, Thank You for the pointers much appreciated. I thought my script was a hodge podge creation. But being new I thought better to work on a piece at a time then try to condense some later.I just keep running into road blocks trying to add code after code to this script.

Still Learning...

Anyway Thank You for your time and help much appreciated!!!!

Mike

OK Mike, glad to help.

Yes we've all been there: it's part of the learning process. You describe the process very well. It seems easier just to keep adding and modifying code as one comes across problems, when usually the best approach is to step back and start again from basics.

Breaking code down into small easily read and understandable procedures is the single best piece of advice I can give. Not least because it's a lot more efficient, particularly when you develop larger applications. Small procedures that concentrate on just one thing can be called over and over again, and are particularly flexible when you pass variables or other parameters to then along with the CALL() statement.

Good luck

R.