-3

Created a VBA where upon clicking refresh, data is filtered and copied from one worksheet and pasted into another. When I tried to run this, invalid outside procedure occurred.

I had used C:I because C is column where the criteria should be applied and filtered. However, I still need column B (hence the B:I) included in the pasted data in "Tracking Sheet". C is 3. But my question isn't about the range, just background that I know the range is iffy.

Sub Refresh()
   Dim Worksheet1 As Worksheet
   Dim lastrowtop, lastrowbottom As Integer
   Worksheets("AUTOTRACK").Range("C2:I2").AutoFilter _
    field:=3, _
    Criteria1:=Date - 1

  lastrowtop = Worksheets("AUTOTRACK").Cells(3, 2105).End(xlDown).Row
  lastrowbottom = Worksheets("Tracking Sheet").Cells(3,1601).End(xlDown).Row

  Range("C:I" & lastrowtop).Copy Destination:=Worksheets("Tracking 
  Sheet").Range("B:I" & lastrowbottom + 1)

  MsgBox ("Pull is complete")
End Sub
jwiggins
  • 1
  • 1
  • 3
  • pause after getting lastrowtop and lastrowbottom and see what those values are. That might be the problem. – John Muggins Apr 06 '17 at 16:40
  • You have some errors in your `Range` syntax, but that error suggests you have invalid code outside a sub, i.e. before the `Sub` line or after the `End Sub` line. – SJR Apr 06 '17 at 16:42
  • "Invalid outside procedure" means you have code written **outside a procedure scope** that isn't legal. Since the code you provided is a procedure scope, ...your princess is in another castle. – Mathieu Guindon Apr 06 '17 at 16:42
  • Wait a minute, you have *two* questions in here. The title and first paragraph says one thing, the rest of the post says another. On this site, questions and their answers are meant to be searchable and useful to everyone, not just the person asking the question: someone landing here looking for information about the compile error you're getting will not give a crap about your `Range` issues. One *specific* question per post, please. – Mathieu Guindon Apr 06 '17 at 16:53
  • The `Range` question is likely a[nother] duplicate of http://stackoverflow.com/q/17733541/1188513, and `Worksheets("Tracking` | `Sheet").Range(...)` can't legally be split the way you have it either. – Mathieu Guindon Apr 06 '17 at 16:59
  • Sorry the info about the range was me letting you guys know i know that part is wrong, but that shouldn't cause the error message i'm receiving – jwiggins Apr 06 '17 at 17:23
  • Does any of the answers you got address the issue you're having? – Mathieu Guindon Apr 06 '17 at 18:15

2 Answers2

1

Code outside a procedure scope must be at the top of the module, in the declarations section.

As the name implies, that section of a module can only contain declarations, which generally includes things like:

  • Module options, such as Option Explicit or Option Private Module
  • Private, Public and/or Friend field declarations
  • Dim and Const statements
  • Enum and Type declarations
  • Declare statements

Any executable statement in the declarations section is illegal, and will produce that compile-time error:

Compile error: invalid outside procedure

A similar compile-time error is raised when executable statements appear in the module body but outside a procedure scope:

Compile error: Only comments may appear after End Sub, End Function, or End Property

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
0

maybe you're after this

Option Explicit

Sub Refresh()
    Dim lastrowtop As Long, lastrowbottom As Long

    lastrowbottom = Worksheets("Tracking Sheet").Cells(3, 1601).End(xlDown).Row '<--| get "Tracking Sheet" 'lastrowbottom ' row index

    With Worksheets("AUTOTRACK") '<--| reference "AUTOTRACK" sheet
        lastrowtop = .Cells(3, 2105).End(xlDown).Row '<--| get its 'lastrowtop' row index
        With .Range("B1:I" & lastrowtop) '<--| reference columns B:I from row 1 (header) to row 'lastrowtop'
            .AutoFilter field:=2, Criteria1:=Date - 1 '<--| filter on referenced range 2nd column (i.e. column "C") with 'Date-1'
            If Application.WorksheetFunction.Subtotal(103, .Cells) > 0 Then .SpecialCells(xlCellTypeVisible).Copy Worksheets("Tracking Sheet").Range("B" & lastrowbottom + 1) '<--| if any filtered cells other than headers, then copy them and paste to "Tracking Sheet" sheet from cell in column B and row 'lastrowbottom + 1'
        End With
        .AutoFilterMode = False '<--| remove autofilter
    End With
    MsgBox ("Pull is complete")
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28