Correct VB code to close a workbook

NickW · Jun 16th 2013

Firstly I hope I've placed this in the correct forum.

Secondly, I apologise for the very messy code below, its a result of 'gathering' various other code and recording macros to get this to do what I require.

I've got a workbook, with a couple of worksheets in, 1 of which is a dashboard, so the macro below grabs the staff listed in the other worksheet copies them to a new one then filters the list and returns them, once its done its sorts into alaphbetical order and then copies a formula down each adjacent cell.

As I'm sure many will tell, I'm not great with VB scripting, but this does work, it worked much better before I remembered I had to share the workbook, hence the copy out to a new sheet. The bit I'm stuck on is

Code

Workbooks(Temp).Close SaveChanges:=False

For some reason it just errors, I've tried various combinations but I just can't get it to work.

Any help greatly appreciated, the whole script is below.

Thanks

Nick

Code

Sub tester_filter()
 'Filter list of testers from Plan to dashboard and show how many line items each one is working on




'Set file path for temp worksheet (if it doesn't exits just create temp.xls and enter file path below)
Dim ThisFileName, Temp As String




 ThisFileName = ActiveWorkbook.Name
 Sheets("Dashboard").Select
 Range("j2").Select
 Temp = ActiveCell
 
'Check that filename has been set
    If Temp = "" Then
    MsgBox "Please set the temp file location before you continue", vbCritical, "File Location"
    End
    Else
        
    End If
    
If Dir(Temp) <> "" Then
    MsgBox "Temp file found. Click OK to continue", vbInformation, "Success"
Else
    MsgBox "Temp file doesn't exist, please check the file path & name and try again", vbCritical, "File not found"
    End
End If




    
'Turn off screen flicker
    Application.ScreenUpdating = False
'Select worksheet dashboard and delete current list
    Sheets("Dashboard").Select
    Rows("24:100").Select
    Selection.Delete Shift:=xlUp
'Select Plan and range of testers
    Sheets("Plan").Select
    Range("D3:D200").Select
    Selection.Copy
 'Filter range to new sheet
    Workbooks.Open Filename:=Temp
    Range("a1").Select
    ActiveSheet.Paste
    Range("a1:a200").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "b1"), Unique:=True
'Select range on sheet
    Range("b2:b200").Select
    Selection.Copy
'Select UAT Prep dashboard worksheet and paste new filtered list
    'Windows("New UAT Prep Sheet - Sandbox.xlsm").Activate
    Windows(ThisFileName).Activate
        Sheets("Dashboard").Select
    Range("B24").Select
    ActiveSheet.Paste
'Sort list into alphabetical order
    ActiveWorkbook.Worksheets("Dashboard").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Dashboard").Sort.SortFields.Add Key:=Range("B24") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Dashboard").Sort
        .SetRange Range("B24:B50")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Add Auto Filter to Plan Sheet
    Sheets("Plan").Select
    Range("A2:T2").Select
    Selection.AutoFilter
'Select dashboard, update formula and draw borders
    Sheets("Dashboard").Select
    ActiveWindow.ScrollColumn = 1
    RowCount = Application.WorksheetFunction.CountA(Range("b24:b100"))
    Range("c24").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Plan!R3C4:R200C4,RC[-1])"
    Range("c24").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("c24").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("c24").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("c24").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("c24:c" & RowCount + 23).Select
       With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
       End With
    Selection.FillDown
    Range("a1").Select
'Close temp work sheet




    Workbooks(Temp).Close SaveChanges:=False
   'Turn on screen updating
    Application.ScreenUpdating = True
    
End Sub

Display More

patel · Jun 16th 2013

Re: Correct VB code to close a workbook

Code

set Wbtemp=Workbooks.Open Filename:=Temp
......................
.....................
    Wbtemp.Close SaveChanges:=False

NickW · Jun 17th 2013

Re: Correct VB code to close a workbook

I must be missing something, as that code doesn't work.

**rory** · Jun 17th 2013

Re: Correct VB code to close a workbook

I'm not really sure why you bother with an additional workbook, but perhaps this will work (air code):

Code

Sub tester_filter()
'Filter list of testers from Plan to dashboard and show how many line items each one is working on




'Set file path for temp worksheet (if it doesn't exits just create temp.xls and enter file path below)
   Dim Temp                   As String
   Dim wbTemp                 As Workbook
   Dim wbSource               As Workbook
   Dim RowCount               As Long


   Set wbSource = ActiveWorkbook


   Temp = wbSource.Sheets("Dashboard").Range("j2").Value


   'Check that filename has been set
   If Temp = "" Then
      MsgBox "Please set the temp file location before you continue", vbCritical, "File Location"
      Exit Sub


   End If


   If Dir(Temp) <> "" Then
      MsgBox "Temp file found. Click OK to continue", vbInformation, "Success"
   Else
      MsgBox "Temp file doesn't exist, please check the file path & name and try again", vbCritical, "File not found"
      Exit Sub
   End If


   'Turn off screen flicker
   Application.ScreenUpdating = False


   ' open temp workbook
   Set wbTemp = Workbooks.Open(Filename:=Temp)
   'Select worksheet dashboard and delete current list
   With wbSource
      .Sheets("Dashboard").Rows("24:100").Delete Shift:=xlUp
      'Copy range of testers to new sheet
      .Sheets("Plan").Range("D3:D200").Copy wbTemp.ActiveSheet.Range("A1")
   End With
   With wbTemp.ActiveSheet
      .Range("a1:a200").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range( _
                                                                          "b1"), Unique:=True
      ' Paste new filtered list to dashboard
      .Range("b2:b200").Copy Destination:=wbSource.Sheets("Dashboard").Range("B24")
   End With
   ' close temp book
   wbTemp.Close SaveChanges:=False


   'Sort list into alphabetical order
   With wbSource
      With .Worksheets("Dashboard").Sort
         With .SortFields
            .Clear
            .Add Key:=wbSource.Worksheets("Dashboard").Range("B24"), SortOn:=xlSortOnValues, _
                 Order:=xlAscending, DataOption:=xlSortNormal
         End With
         .SetRange wbSource.Worksheets("Dashboard").Range("B24:B50")
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
      'Add Auto Filter to Plan Sheet
      .Sheets("Plan").Range("A2:T2").AutoFilter
      'Select dashboard, update formula and draw borders
      With .Sheets("Dashboard")
         RowCount = Application.WorksheetFunction.CountA(.Range("b24:b100"))
         With .Range("c24:c" & RowCount + 23)
            .FormulaR1C1 = "=COUNTIF(Plan!R3C4:R200C4,RC[-1])"
            .Borders.LineStyle = xlContinuous
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
         End With
      End With
   End With


   'Turn on screen updating
   Application.ScreenUpdating = True


End Sub

Display More

NickW · Jun 17th 2013

Re: Correct VB code to close a workbook

I managed to fix it, I realised where I was going wrong too.

Code

FileToOpen = ActiveCell

That was bringing back the whole file name e.g. c:dir/dir/filename.xls

Where as what I needed was just filename.xls

so I just added this within the code when its in the workbook I need, I know its probably a bit sloppy still but it works for me;

Code

temp = ActiveWorkbook.Name

NickW · Jun 17th 2013

Re: Correct VB code to close a workbook

Hi Rory, thanks for the reply. Is there an easier whay to do it then without opening a 'temp' workbook. I only did this as the once the workbook is shared the marco stoppped working.

And thanks for the above code, it does indeed solve the problem

Nick

**rory** · Jun 17th 2013

Re: Correct VB code to close a workbook

You should be able to simply filter straight from one sheet in the workbook to the other. Although now that you mention shared workbooks (shudder) all bets are off...

NickW · Jun 17th 2013

Re: Correct VB code to close a workbook

Yeah I know, not ideal.

I guess I could just create a new sheet then delte or not save the new sheet once the working out has finished?

Nick

NickW · Jun 17th 2013

Re: Correct VB code to close a workbook

So, just in case it helps anyone else out, I ended up with this.

Code

Sub tester_filter()
' Filter list of testers from Plan to dashboard and show how many line items each one is working on
     
     
' Set file path for temp worksheet (if it doesn't exits just create temp.xls and enter file path below)
    Dim Temp                   As String
    Dim NewBook                 As Workbook
    Dim wbSource               As Workbook
    Dim RowCount               As Long
     
    Set wbSource = ActiveWorkbook
     
'No longer needed
'Temp = wbSource.Sheets("Dashboard").Range("j2").value
     
' Check that filename has been set
'    If Temp = "" Then
'        MsgBox "Please set the temp file location before you continue", vbCritical, "File Location"
'        Exit Sub
         
'    End If
     
'    If Dir(Temp) <> "" Then
'        MsgBox "Temp file found. Click OK to continue", vbInformation, "Success"
'    Else
'        MsgBox "Temp file doesn't exist, please check the file path & name and try again", vbCritical, "File not found"
'        Exit Sub
'    End If
     
' Turn off screen flicker
    Application.ScreenUpdating = False
     
' open temp workbook
'    Set NewBook = Workbooks.Open(Filename:=Temp)
'   create a new workbook
    Set NewBook = Workbooks.Add
    With NewBook
     
    End With
' Select worksheet dashboard and delete current list
    With wbSource
        .Sheets("Dashboard").Rows("24:100").Delete Shift:=xlUp
'Copy range of testers to new sheet
        .Sheets("Plan").Range("D3:D200").Copy NewBook.ActiveSheet.Range("A1")
    End With
    With NewBook.ActiveSheet
        .Range("a1:a200").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range( _
        "b1"), Unique:=True
' Paste new filtered list to dashboard
        .Range("b2:b200").Copy Destination:=wbSource.Sheets("Dashboard").Range("B24")
    End With
' close temp book
    NewBook.Close SaveChanges:=False
     
' Sort list into alphabetical order
    With wbSource
        With .Worksheets("Dashboard").Sort
            With .SortFields
                .Clear
                .Add Key:=wbSource.Worksheets("Dashboard").Range("B24"), SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortNormal
            End With
            .SetRange wbSource.Worksheets("Dashboard").Range("B24:B50")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
 ' Add Auto Filter to Plan Sheet
        .Sheets("Plan").Range("A2:T2").AutoFilter
 ' Select dashboard, update formula and draw borders
        With .Sheets("Dashboard")
            RowCount = Application.WorksheetFunction.CountA(.Range("b24:b100"))
            With .Range("c24:c" & RowCount + 23)
                .FormulaR1C1 = "=COUNTIF(Plan!R3C4:R200C4,RC[-1])"
                .Borders.LineStyle = xlContinuous
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
            End With
        End With
    End With
     
' Turn on screen updating
    Application.ScreenUpdating = True
     
End Sub

Display More

Correct VB code to close a workbook

Participate now!

Share