0

In the code below, it is failing at the "FORMAT TEMPLATE" comment. I get a run time error 1004. What I am doing is - I am closing the previous window (from copying the template) and pasting it into the good workbook. I don't think I am switching workbooks properly.

Sub BBUorders()
'OPEN TEMPLATE
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "C:\Users\douglas.futato\Desktop\"
sFile = sPath & "BBU_CMD_TEMPLATE.xlsx"
Set wb = Workbooks.Open(sFile)

'COPY TEMPLATE PASTE IN BBU DOC
Dim tmplt As Workbook
On Error Resume Next
    Set tmplt = Workbooks("BBU_CMD_TEMPLATE.xlsx")
    If tmplt Is Nothing Then
        MsgBox "Template file needs to be open..."
        Exit Sub
    End If
On Error GoTo 0
With ThisWorkbook
    tmplt.ActiveSheet.Copy After:=.Sheets(.Sheets.Count)
End With

'CLOSE TEMPLATE
Windows("BBU_CMD_TEMPLATE.xlsx").Activate
ActiveWindow.Close False

'FORMAT TEMPLATE --------- THIS IS WHERE IS BOMBS OUT
ThisWorkbook.Activate
Worksheets(“Sheet3”).Activate
ActiveCell.Offset(-3, -8).Range("A1:H1").Select
Selection.AutoFilter
Cells.Replace What:="[BBU_CMD_TEMPLATE.xlsx]Price List", Replacement:=Sheet1.Name, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
ActiveSheet.Range("$A$1:$H$61").AutoFilter Field:=6, Criteria1:="0"
Rows("2:100").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-18
ActiveSheet.Range("$A$1:$H$18").AutoFilter Field:=6
ActiveSheet.Range("$A$1:$H$18").AutoFilter Field:=7, Criteria1:="0"
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$H$17").AutoFilter Field:=7
Range("E2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E50"), Type:=xlFillDefault
Range("E2:E21").Select
Range("I1").Select
ActiveSheet.Range("$A$1:$H$50").AutoFilter Field:=1, Criteria1:="="
Rows("8:100").Select
Selection.Delete Shift:=xlUp
Range("A2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveSheet.Range("$A$1:$H$7").AutoFilter Field:=1
Range("A1").Select
End Sub
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Doug Fresh
  • 79
  • 1
  • 12
  • 1
    First thought would be...do you need that line at all? If you only have 2 workbooks open, and you close one...there's only one left. Secondly, cleaning that recorder code up with [Avoid Select/Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) will help make it much easier to read and follow. – Rdster May 09 '17 at 18:24
  • I am new to VBA, I am learning - even removing "ThisWorkbook.Activate Worksheets(“Sheet3”).Activate" it still produces the same error – Doug Fresh May 09 '17 at 18:33
  • The code isn't bombing on a comment. Is it the `ActiveWindow.Close` statement or the `ThisWorkbook.Activate` statement that's blowing up? In any case, your code relies way too much on `.Select` and `.Activate` to be anywhere near stable. – Mathieu Guindon May 09 '17 at 18:51
  • what you using 'ActiveCell.Offset(-3, -8).Range("A1:H1").Select' I guess "Range("A1:H1").Select" also work – Unknown May 09 '17 at 18:57
  • `Worksheets(“Sheet3”).Activate` will crash because `“Sheet3”` is a completely different thing to `"Sheet3"` - `“Sheet3”` is a variable name - `"Sheet3"` is a string literal. See the answer by Paul Co – YowE3K May 09 '17 at 19:54

2 Answers2

1

What I noticed is the Worksheets(“Sheet3”).Activate
Have you tried replacing it with Worksheets("Sheet3").Activate

The ActiveCell.Offset should not contain negative value.
e.g. If your active cell is (1, 1), you will get an error because
-3 - 1 is less than 1, and is not a valid row. (same with column)

Paul Co
  • 447
  • 2
  • 9
  • 1
    Your first paragraph will be the answer to the problem - `“Sheet3”` is a variable name which won't have been assigned a value so will be `Empty`, and `Worksheets(Empty).Activate` isn't going to work. – YowE3K May 09 '17 at 19:52
  • Ah, the joys of copy+pasta programming! Nice catch! – Mathieu Guindon May 09 '17 at 20:10
0

I know this isn't the cleanest code, but I figured out how to make it run. I had to add the worksheet as a reference. It was defaulting to sheet 2 when I was trying to work in the new sheet. Example: Worksheets("TEMPLATE").Range("E45").Select

Sub templateToBBU()
'OPEN TEMPLATE
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "C:\Users\douglas.futato\Desktop\"
sFile = sPath & "BBU_CMD_TEMPLATE.xlsx"
Set wb = Workbooks.Open(sFile)

'COPY TEMPLATE PASTE IN BBU DOC
Dim tmplt As Workbook
On Error Resume Next
    Set tmplt = Workbooks("BBU_CMD_TEMPLATE.xlsx")
    If tmplt Is Nothing Then
        MsgBox "Template file needs to be open..."
        Exit Sub
    End If
On Error GoTo 0
With ThisWorkbook
    tmplt.ActiveSheet.Copy After:=.Sheets(.Sheets.Count)
End With

'CLOSE TEMPLATE
Windows("BBU_CMD_TEMPLATE.xlsx").Activate
ActiveWindow.Close False
End Sub
Sub Macro1()
Worksheets("TEMPLATE").Range("A1:H1").Select
Selection.AutoFilter
Worksheets("TEMPLATE").Cells.Replace What:= _
    "C:\Users\douglas.futato\Desktop\[BBU_CMD_TEMPLATE.xlsx]Price List", _
    Replacement:=Sheet1.Name, LookAt:=xlPart, SearchOrder:=xlByRows,     
MatchCase _
    :=False, SearchFormat:=False, ReplaceFormat:=False
Worksheets("TEMPLATE").Range("$A$1:$H$61").AutoFilter Field:=6, 
Criteria1:="0"
Worksheets("TEMPLATE").Rows("2:100").Select
Selection.Delete Shift:=xlUp
Worksheets("TEMPLATE").Range("$A$1:$H$18").AutoFilter Field:=6
Worksheets("TEMPLATE").Range("$A$1:$H$18").AutoFilter Field:=7, 
Criteria1:="0"
Selection.Delete Shift:=xlUp
Worksheets("TEMPLATE").Range("$A$1:$H$17").AutoFilter Field:=7
Worksheets("TEMPLATE").Range("E2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E5").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E6").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E7").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E8").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E9").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E10").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E11").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E13").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E14").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E15").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E16").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E17").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E18").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E19").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E20").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E22").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E23").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E24").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E25").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E26").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E27").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E28").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E29").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E30").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E31").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E32").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E33").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E34").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E35").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E36").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E37").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E38").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E39").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E40").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E41").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E42").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E43").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E44").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E45").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E46").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E47").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E48").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E49").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E50").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Worksheets("TEMPLATE").Range("E51").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"

'ST and Cust Mapping HERE
Worksheets("TEMPLATE").Range("$A$1:$H$50").AutoFilter Field:=1, 
Criteria1:="="
Worksheets("TEMPLATE").Rows("8:100").Select
Selection.Delete Shift:=xlUp
Worksheets("TEMPLATE").Range("$A$1:$H$50").AutoFilter Field:=1


End Sub

Sub Main()
Call templateToBBU
Call Macro1
End Sub
Doug Fresh
  • 79
  • 1
  • 12
  • Ok. Now read [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and stop writing macro-recorder code. – Mathieu Guindon May 09 '17 at 20:11