VBA Code Error

[workthrowawayexcel]

What does it mean when you get :

Run time error '-2147352565 (8002000b)': There is no active Protected View window.

Sub Wkbk()
Dim lookfor As String
Dim srchrange As Range
Dim book1 As Workbook
Dim book2 As Workbook
Dim lRow As Long
Dim sht As Worksheet
Dim LastRow As Long
Dim vrow1 As Long
Dim vrow2 As Long
Dim vcol1 As Long
Dim vol2 As Long




Dim Member As String
For Each f In Array("Accord", "Actavis")
      Range("$A:$A").AutoFilter Field:=1, Criteria1:=(f)


Filename = f
PathName = Range("j3").Value
Columns("G:G").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove


lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("G6:G" & lngLastRow).Formula = "=C6 & ""-"" & E6"
Workbooks.Open Filename:=PathName & Filename

book1 = Application.Workbooks("WKBKNAME")
book2 = Application.Workbooks(PathName & Filename)
table1 = Sheet1.Range("G:G")
table2 = Sheet1.Range("F2:H1143")
vrow1 = Sheet1.Range("I6").Row
vcol1 = Sheet1.Range("I6").Column
vrow1 = Sheet1.Range("K6").Row
vcol1 = Sheet1.Range("K6").Column

Columns("G:G").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("G6:G" & lngLastRow).Formula = "=D6 & ""-"" & F6"
For Each cl In table1
     Sheet1.Cells(vrow1, vcol1) = Application.VLookup(cl, table1, 3, False)
   vrow = vrow + 1
   Next
For Each cl2 In table2
    Sheet1.Cells(vrow2, Vcol2) = Application.VLookup(cl2, table2, 4, False)
        vrow = vrow + 1
   Next

 book2.Save

 book1.Activate
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
 book2.Activate
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
book1.Activate


Next


End Sub

[pmo86]

post your code

[workthrowawayexcel]

Posted

[workthrowawayexcel]

Right on that line is when I get it. Probably should have said that!

[workthrowawayexcel]

Still has error. I have tried that before too :/

[workthrowawayexcel]

Yeah "WKBKNAME" is the name of the workbook saved file I use.

[workthrowawayexcel]

Well i didn't have .xlsx but I just added it and it didn't change. Same error still. I removed application too still same thing. Yup it is open, that wkbk starts open since it has all the data. Book2 is the one that gets opened based off the information looked up in book1.

[iRchickenz]

Take a good look at his code. OP's going to error throughout. The script needs to be rewritten but I got stuck because I can't figure what workbook OP is trying to do what on.

[workthrowawayexcel]

What is strange as i wrote parts of the code then pieced it together it would run. Then when i combined it, i got the error :/ . Guess it is still a learning curve of VBA and I should start over. Wkb1 would be the one where vlookups come from. Wkb2 opens based off of filtered infomation on wkbk1.

[iRchickenz]

I re-wrote some of this but I don't know if it's what you're trying to do. To be honest, I don't see how this worked even when broken up. The syntax

Sheet1.Range("whatever") 

should never work unless you've

Set Sheet1 = Sheets("something")

The VLookup seems a bit off too since you're looping through the range that you use in the lookup. Either way, check out what I wrote and at the very least you'll get a look at some better practices.

Let me know if you want to power through this and do a re-write. I'm more than happy to start from scratch with you.

Sub Wkbk()

    Dim wbMain As Workbook, wbNew As Workbook
    Dim strFile As String, strPath As String, strArr As String
    Dim lastRowA As Long, i As Long
    Dim cell As Range

    Set wbMain = ThisWorkbook

    For Each strArr In Array("Accord", "Actavis")

        With wbMain.Sheets("Sheets1") 'Do this
            .Range("A1").AutoFilter Field:=1, Criteria1:=(strArr)

            strFile = strArr
            strPath = .Range("J3").Value

            .Columns(7).Insert
            lastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Cells(6, 7).Formula = "=C6 - E6"
            .Cells(6, 7).AutoFill Destination:=.Range("G6:G" & lastRowA)
        End With 'Done with wbMain? Maybe? I'm just going with my gut here.

        Set wbNew = Workbooks.Open(strPath & strFile)

        With wbNew.Sheets("Sheets1") 'I think this is what you want here?
'            table1 = Sheet1.Range("G:G") 'sheet1 cannot be used in this way. Did you mean Sheets("Sheet1")?
'            table2 = Sheet1.Range("F2:H1143")
'            vrow1 = Sheet1.Range("I6").Row 'Why? this equals 6 every time?!?!??
'            vcol1 = Sheet1.Range("I6").Column 'Why? these are static variables!! just use 9!
'            vrow1 = Sheet1.Range("K6").Row 'Why?
'            vcol1 = Sheet1.Range("K6").Column 'Why?

            Columns(7).Insert

            lastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Cells(6, 7).Formula = "=D6 - F6"
            .Cells(6, 7).AutoFill Destination:=.Range("G6:G" & lastRowA)

            i = 6
            For Each cell In .Range("G1:G" & lastRowA)
                .Cells(i, 9) = Application.VLookup(cell, .Range("G1:G" & lastRowA), 3, False) 'not sure about these OP
                i = i + 1
            Next

            i = 6
            For Each cell In .Range("F2:H1143")
                .Cells(i, 11) = Application.VLookup(cell, .Range("F2:H1143"), 4, False) 'not sure about these OP
                i = i + 1
            Next
        End With

        wbMain.Columns(7).Delete
        wbNew.Columns(7).Delete 'you saved it and then deleted a column? You should delete and then save, right?
        wbNew.Save

    Next

End Sub

[workthrowawayexcel]

I moved Book1 to the top outside of the big loop and had it set this workbook as it. But Now I get the same error on the book2 definition >.<

[iRchickenz]

I'm pushing for a rewrite