Results 1 to 6 of 6

Restart Sub when Finished

Threaded View

  1. #1
    Forum Contributor
    Join Date
    10-11-2013
    Location
    Austria
    MS-Off Ver
    Excel 2010
    Posts
    136

    Restart Sub when Finished

    I'm copying info from about 2000 Sheets in around 200 Workbooks, and lazy as I am, rather that pressing 2 keys (I'm using ALT+1 to run the macro at the moment) to start the process for each sheet I'd like it to run the Sub then restart.
    The code starts in the worksheet(W1) where I copy the info from, copys it, pastes it in the other workbook(W2), closes W1 and opens the next W1 from a list of links in W2.
    Then stops.
    how can I make the whole process start again automatically?

    Here's the code (messy as it is..)

     
    
    Sub Copy_Rech()
    '
    ' Copy_Rech Makro
    '
    ' Tastenkombination: Strg+b
    '
    Dim IB As String
    Dim IB2 As String
    Dim Wkbk As Workbook
    Dim RechWkbk As Workbook
    Dim Rechrange As String
    Dim Rechnung As String
    Set Wkbk = ActiveWorkbook
    Set RechWkbk = Workbooks("2014 Hyperlinks.xlsx")
    Dim FindName As Range
    Dim FindEnd As Range
    Dim ManStart As Range
    Dim ManEnd As Range
    Dim CopyRange As Range
    
    Workbooks("2014 Hyperlinks.xlsx").Activate
    IB = Application.WorksheetFunction.Index(Range("J1:J200"), Application.WorksheetFunction.Match(ActiveCell.Offset(0, -37), Range("I1:I200"), 0))
    If MsgBox("Sheet=" & IB, vbYesNo) = vbYes Then
        Sheets(IB).Select
        Else
        IB2 = Application.InputBox("Sheet=", Type:=2)
        If MsgBox("Sheet=" & IB2, vbYesNo) = vbYes Then
        Sheets(IB2).Select
        End If
    End If
    Application.ScreenUpdating = False
    Wkbk.Activate
    On Error GoTo FindErr:
    Set FindName = Range("A:A").Find(What:="Name").Offset(2)
    Set FindEnd = Range("C:E").Find(What:="Place2").Offset(-2)
    Set CopyRange = Range(FindName.EntireRow, FindEnd.EntireRow)
    
    Copy1:
    CopyRange.Copy
    
    RechWkbk.Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.EntireRow.Select
    ActiveSheet.Paste
      Application.CutCopyMode = False
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    
    
    Wkbk.Activate
    Wkbk.Close False
    Workbooks("2014 Hyperlinks.xlsx").Activate
    Sheets("Übersicht").Activate
    If ActiveCell.Offset(0, 1).Value = 0 Then
    ActiveCell.EntireRow.Hidden = True
    Range("BC" & (ActiveCell.Row)).Offset(1).Select
    Else
    ActiveCell.Offset(0, 1).Select
    End If
        
    Rechnung = Range("N" & (ActiveCell.Row))
     Rechrange = ActiveCell.Offset(0, -37)
        
        Workbooks.Open fileName:= _
           Range("o" & (ActiveCell.Row))
        ActiveWindow.Visible = False
        Windows(Rechnung).Visible = True
            Application.Goto Reference:=Worksheets(Rechrange).Range("G20")
            
    Application.ScreenUpdating = True
    Exit Sub
    
    
    FindErr:
    Set ManStart = Application.InputBox("Where to Start?", Type:=8)
    Set ManEnd = Application.InputBox("Where to stop?", Type:=8)
    Set CopyRange = Range(ManStart.EntireRow, ManEnd.EntireRow)
    Resume Copy1
    End Sub
    Last edited by rodgersmg; 05-08-2015 at 03:02 AM. Reason: Reformat

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform restart after end sub
    By Sibrulotte in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2011, 02:34 PM
  2. Re: 'Do Until...' Restart
    By Jack in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-22-2006, 04:10 AM
  3. [SOLVED] Re: 'Do Until...' Restart
    By Jack in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-21-2006, 12:32 PM
  4. 'Do Until...' Restart
    By Jack in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2006, 11:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1