Newbie VBA- Close Variable Defined Workbook

[ackalbo]

Hello all

I started to learn VBA code on Excel 2010 10 days ago and have "successfully" written the following basic code:

Sub OpenWorkbook()


'Opens Workbook & Copy/Paste Data

Dim FilePath As String
Dim FName As String

FilePath = "C:\Users\Kalbo\Desktop\Charonja Lollipop\LPOP\CashierSS\"
FName = FilePath & "CashierSS " & Format(Date - 1, "MM-DD-YYYY") & ".xlsm"

Workbooks.Open FName
Sheets("Data Entry").Select
Range("A3:F1003").Select
Selection.Copy

Workbooks("LZ Daily Sales Breakdown.xlsm").Activate
Sheets("Data Entry").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Daily Overview").Select
Range("B3:E126").Select
Selection.Copy

Sheets("Monthly Overview").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Daily Overview").Select
Range("H3:K126").Select
Selection.Copy

Sheets("Monthly Overview").Select
Range("B132").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Data Entry").Select
Range("A3:F1003").Select
Selection.ClearContents


Workbooks(FName).Close False

This works great for what I need it to do except for the final line For the life of me I cannot seem to be able to close the variable defined workbook....I always get the error msg "Run-time error '9':Subscript out of range". I have tried everything that I can find on the internet but to no avail. I know that I am almost certainly missing something obvious (even consulted VBA Programming For Dummies) but as of now I cannot seem to get the last line to work.

Any assistance would be greatly appreciated. Many thanks guys & gals.

 

[galt13]

Hello all

I started to learn VBA code on Excel 2010 10 days ago and have "successfully" written the following basic code:



This works great for what I need it to do except for the final line For the life of me I cannot seem to be able to close the variable defined workbook....I always get the error msg "Run-time error '9':Subscript out of range". I have tried everything that I can find on the internet but to no avail. I know that I am almost certainly missing something obvious (even consulted VBA Programming For Dummies) but as of now I cannot seem to get the last line to work.

Any assistance would be greatly appreciated. Many thanks guys & gals.

At a guess your workbook name is wrong or the book is already closed

 

[galt13]

At a guess your workbook name is wrong or the book is already closed

You also don't need the word "false" at the end

 

[Norie]

Try this.

Sub OpenWorkbook()
Dim wbOpen As Workbook

Dim FilePath As String
Dim FName As String

   'Opens Workbook & Copy/Paste Data

   FilePath = "C:\Users\Kalbo\Desktop\Charonja Lollipop\LPOP\CashierSS\"
   FName = FilePath & "CashierSS " & Format(Date - 1, "MM-DD-YYYY") & ".xlsm"

   Set wbOpen =  Workbooks.Open(FName)

    ' other code 

   wbOpen.Close SaveChanges:=False

 

[ackalbo]

Wow thank you Norie, worked like a charm. Looking at it, it's so simple but I would have never thought to do that. Thank you too galt13.

It's an uphill struggle but worth it hehe.

Many thanks.