Code behaving differently after additional code

[dreid1011]

Good afternoon,

I have this little snippet of code I wrote earlier today, and all was well for the intended purpose, until I added more lines above the original portion.

So, what this is supposed to do is:

Copy data from "Vet List" columns B, D & C where column Q is blank. Rows 5 to the end of data. This data is copied to "Export Active Ex" columns A, B & C respectively. Rows 2 until it's done.


This part is supposed to copy sheet "Export Active Ex" and save it as a new workbook named "Active Exemptions " & date/time & ".xlsx" with ONLY sheet "Export Active Ex" in it.

Private Sub ExportActiveList()
Dim wsAc As Worksheet, wsEx As Worksheet
Dim i As Long, nRow As Long, lRow As Long
Set wsAc = Sheets("Vet List")
Set wsEx = Sheets("Export Active Ex")
lRow = wsAc.Range("B" & Rows.Count).End(xlUp).Row
nRow = 2
Application.ScreenUpdating = False
wsEx.Range("A2:C9999").Clear
For i = 5 To lRow
    If wsAc.Range("Q" & i).Value = "" Then
        wsEx.Range("A" & nRow).Value = wsAc.Range("B" & i).Value
        wsEx.Range("B" & nRow).Value = wsAc.Range("D" & i).Value
        wsEx.Range("C" & nRow).Value = wsAc.Range("E" & i).Value
        nRow = nRow + 1
    End If
Next i
wsEx.Copy
wsEx.SaveAs Filename:="F:\Documents\VETERANS\ActiveExemptions " & CDbl(Now()) & ".xlsx", FileFormat:=51
Application.ScreenUpdating = True
End Sub

The code in red above was working as I explained and not causing any trouble. After I added the portion in blue, the new workbook that is supposed to only have sheet "Export Active Ex" in it, saves with all the sheets from the parent workbook, and then I get a extra workbook, with the single sheet as desired, named "Book #" and an error message telling me I can't save it as non-VBA while it has a "VB Project".

Can anyone see any reason why this might happen after adding the code in blue? The single sheet "Export Active Ex" has no code in it's module at all. This is making no sense to me.

Any help is much appreciated.

 

[Fluff]

Try changing
wsEx.SaveAs
to
ActiveWorkbook.SaveAs

 

[dreid1011]

Try changing
wsEx.SaveAs
to
ActiveWorkbook.SaveAs

Thank you, that worked. It doesn't make sense to me WHY it worked, but it did. But computer logic doesn't always make sense to me half the time anyways.

 

[Fluff]

Glad to help & thanks for the feedback.

FYI
This line

wsEx.Copy

Creates a copy of the sheet in a new workbook. As it's creating a new workbook, that workbook will automatically be active.
However this line

wsEx.SaveAs Filename:="F:\Documents\VETERANS\ActiveExemptions " & CDbl(Now()) & ".xlsx", FileFormat:=51

is saving the entire existing workbook.
HTH

 

[dreid1011]

Glad to help & thanks for the feedback.

FYI
This line

wsEx.Copy

Creates a copy of the sheet in a new workbook. As it's creating a new workbook, that workbook will automatically be active.

And that is why it didn't make sense. I was unaware of all that .Copy was actually doing.