VBA doesn't close

[jas1980]

In the following code (this is at the end of the macro) the opened csv doesn't close when the code above Else runs, but when the code below Else runs it does, the csv is saved because when I close there is no request to save and all data remains.

Is there a way to change this so it saves and closes without me having to after this code finishes?


filetoopen = ThisWorkbook.Worksheets("CSV COLLECT").Range("V3")
Workbooks.Open fileName:=filetoopen
If Not IsEmpty(Range("A1")) = True Then
ThisWorkbook.Worksheets("COLLECT HIS").Range("C9").Copy
Range("A1").End(xlDown).Offset(1).PasteSpecial xlValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CalculateFullRebuild
For Each wsheet In ThisWorkbook.Worksheets
wsheet.PROTECT Password:="TRADE"
Next wsheet
ThisWorkbook.Worksheets("COLLECT HIS").Activate
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

Else
End If


filetoopen = ThisWorkbook.Worksheets("CSV COLLECT").Range("V3")
ThisWorkbook.Worksheets("COLLECT HIS").Range("C8:C9").Copy
Workbooks.Open fileName:=filetoopen
If IsEmpty(Range("A1")) = True Then
Range("A1").PasteSpecial xlValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CalculateFullRebuild
For Each wsheet In ThisWorkbook.Worksheets
wsheet.PROTECT Password:="TRADE"
Next wsheet
Worksheets("COLLECT HIS").Activate
Application.ScreenUpdating = True
Application.DisplayStatusBar = True





End If
End Sub

 

[Fluff]

If A1 is empty then the first code will jump to the Else then End If, you need to close the workbook in the Else to cover that

filetoopen = ThisWorkbook.Worksheets("CSV COLLECT").Range("V3")
Workbooks.Open Filename:=filetoopen
If Not IsEmpty(Range("A1")) = True Then
ThisWorkbook.Worksheets("COLLECT HIS").Range("C9").Copy
Range("A1").End(xlDown).Offset(1).PasteSpecial xlValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CalculateFullRebuild
For Each wsheet In ThisWorkbook.Worksheets
wsheet.Protect Password:="TRADE"
Next wsheet
ThisWorkbook.Worksheets("COLLECT HIS").Activate
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

Else
ActiveWorkbook.Close

End If

 

[jas1980]

If A1 is empty then the first code will jump to the Else then End If, you need to close the workbook in the Else to cover that

filetoopen = ThisWorkbook.Worksheets("CSV COLLECT").Range("V3")
Workbooks.Open Filename:=filetoopen
If Not IsEmpty(Range("A1")) = True Then
ThisWorkbook.Worksheets("COLLECT HIS").Range("C9").Copy
Range("A1").End(xlDown).Offset(1).PasteSpecial xlValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CalculateFullRebuild
For Each wsheet In ThisWorkbook.Worksheets
wsheet.Protect Password:="TRADE"
Next wsheet
ThisWorkbook.Worksheets("COLLECT HIS").Activate
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

Else
ActiveWorkbook.Close

End If

[/CODE
[/QUOTE]
Thanks for the reply, the current code below else closes the workbook but the code above doesn't, if A1 is empty it needs to copy and paste C8 & C9 into A1 & A2 but, if A1 already contains data it needs to copy C9 into the last empty cell in column A

 

[Fluff]

And?

 

[jas1980]

Thanks for the reply, the current code below else closes the workbook but the code above doesn't, if A1 is empty it needs to copy and paste C8 & C9 into A1 & A2 but, if A1 already contains data it needs to copy C9 into the last empty cell in column A.

 

[Fluff]

Please do not multi quote posts, it just causes a mess. Simply type your reply into the Reply box at the bottom of the thread.

Rather than running the code separately just use

filetoopen = ThisWorkbook.Worksheets("CSV COLLECT").Range("V3")
Workbooks.Open Filename:=filetoopen
If IsEmpty(Range("A1")) = True Then
   ThisWorkbook.Worksheets("COLLECT HIS").Range("C8:C9").Copy
   Range("A1").PasteSpecial xlValues
Else
   ThisWorkbook.Worksheets("COLLECT HIS").Range("C9").Copy
   Range("A1").End(xlDown).Offset(1).PasteSpecial xlValues
End If
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CalculateFullRebuild
For Each wsheet In ThisWorkbook.Worksheets
   wsheet.Protect Password:="TRADE"
Next wsheet
Worksheets("COLLECT HIS").Activate
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

 

[jas1980]

Thank you for this, I will add it now. This way has defiantly condensed the code a lot.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jas1980]

Have used this now and it works brilliantly, I have also removed lines from the start of the code and it runs quicker. Thank you

 

[Fluff]

My pleasure.