Auto Excel workbook close: save= false during an auto subroutine

[tomwashere2]

I have a subroutine (macro) that automatically pulls data from various
workbooks. I would like to auto close each workbook when I am done
extracting the data.

The vba code to do this according to Excel help is:

Workbooks("BOOK1.XLS").Close SaveChanges:=False

The code works when I specify the name such as "BOOK1.XLS" . The problem is
that the file names vary depending on dates etc. So I assign a variable to
identify the names each time it is passed through the loop and incremented.
Therefore the name of each stays the same through a string variable named
"path3" as follows:

While LDR <= DOM

Workbooks.Open Filename:=path3
' extract
Selection.RemoveSubtotal
Range("A2:O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("RwMTD.xls").Activate
Sheets("MTD Data").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveSheet.Paste
Range("A1").Select

' This would be the point at which I would want to close the (Path3) file

' LDR Value
LDR = LDR + 1
If LDR < 0 Or LDR > 31 Then
MsgBox ("Please enter a valid number for the day of month i.e.(1-31)")
End
Else
If LDR > 0 And LDR < 10 Then
LDR = "0" & LDR
Else
LDR = LDR
End If
End If
path2 = "\rpt." & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2
' end extract

Wend

[JMB]

i think there is an issue with path3. it is the complete path right?
(C:\temp\xyz.xls). to close the workbook, i believe you'll need to separate
the workbook name from the rest of the path.

x = Split(path3, "\", -1, vbTextCompare)
Workbooks(x(UBound(x))).Close savechanges:=False

Or, set an object variable = to the file you opened (lets say WkBk) and use

WkBk.Close SaveChanges:=False

"tomwashere2" wrote:

> I have a subroutine (macro) that automatically pulls data from various
> workbooks. I would like to auto close each workbook when I am done
> extracting the data.
>
> The vba code to do this according to Excel help is:
>
> Workbooks("BOOK1.XLS").Close SaveChanges:=False
>
> The code works when I specify the name such as "BOOK1.XLS" . The problem is
> that the file names vary depending on dates etc. So I assign a variable to
> identify the names each time it is passed through the loop and incremented.
> Therefore the name of each stays the same through a string variable named
> "path3" as follows:
>
> While LDR <= DOM
>
> Workbooks.Open Filename:=path3
> ' extract
> Selection.RemoveSubtotal
> Range("A2:O2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Copy
> Windows("RwMTD.xls").Activate
> Sheets("MTD Data").Select
> Range("A1").Select
> Selection.End(xlDown).Select
> ActiveSheet.Paste
> Range("A1").Select
>
> ' This would be the point at which I would want to close the (Path3) file
>
> ' LDR Value
> LDR = LDR + 1
> If LDR < 0 Or LDR > 31 Then
> MsgBox ("Please enter a valid number for the day of month i.e.(1-31)")
> End
> Else
> If LDR > 0 And LDR < 10 Then
> LDR = "0" & LDR
> Else
> LDR = LDR
> End If
> End If
> path2 = "\rpt." & Year & "." & month2 & "." & LDR & ".xls"
> path3 = path1 & path2
> ' end extract
>
> Wend

[William Benson]

How can tomwashere2 refer to Path3 before he gets to the code where it is
assigned. Isn't it = "" in the first iteration?

By the way, I love Split, I never knew about it.

B.

"JMB" <[email protected]> wrote in message
news:[email protected]...
>i think there is an issue with path3. it is the complete path right?
> (C:\temp\xyz.xls). to close the workbook, i believe you'll need to
> separate
> the workbook name from the rest of the path.
>
> x = Split(path3, "\", -1, vbTextCompare)
> Workbooks(x(UBound(x))).Close savechanges:=False
>
> Or, set an object variable = to the file you opened (lets say WkBk) and
> use
>
> WkBk.Close SaveChanges:=False
>
> "tomwashere2" wrote:
>
>> I have a subroutine (macro) that automatically pulls data from various
>> workbooks. I would like to auto close each workbook when I am done
>> extracting the data.
>>
>> The vba code to do this according to Excel help is:
>>
>> Workbooks("BOOK1.XLS").Close SaveChanges:=False
>>
>> The code works when I specify the name such as "BOOK1.XLS" . The problem
>> is
>> that the file names vary depending on dates etc. So I assign a variable
>> to
>> identify the names each time it is passed through the loop and
>> incremented.
>> Therefore the name of each stays the same through a string variable named
>> "path3" as follows:
>>
>> While LDR <= DOM
>>
>> Workbooks.Open Filename:=path3
>> ' extract
>> Selection.RemoveSubtotal
>> Range("A2:O2").Select
>> Range(Selection, Selection.End(xlDown)).Select
>> Selection.Copy
>> Windows("RwMTD.xls").Activate
>> Sheets("MTD Data").Select
>> Range("A1").Select
>> Selection.End(xlDown).Select
>> ActiveSheet.Paste
>> Range("A1").Select
>>
>> ' This would be the point at which I would want to close the (Path3)
>> file
>>
>> ' LDR Value
>> LDR = LDR + 1
>> If LDR < 0 Or LDR > 31 Then
>> MsgBox ("Please enter a valid number for the day of month
>> i.e.(1-31)")
>> End
>> Else
>> If LDR > 0 And LDR < 10 Then
>> LDR = "0" & LDR
>> Else
>> LDR = LDR
>> End If
>> End If
>> path2 = "\rpt." & Year & "." & month2 & "." & LDR & ".xls"
>> path3 = path1 & path2
>> ' end extract
>>
>> Wend

[JMB]

I figured he posted only a portion of the code and had already defined path3
for the first iteration. He did say he tried the close method with the
specific filename and it worked, so it must have been defined somewhere.

Split and Join are nice for separating paths from filenames.

On the filename issue, he could also capture the filename right after
opening the file

x = activeworkbook.name
...
...
...
workbooks(x).close savechanges:=false


Hopefully, this'll post w/o "We're sorry........" (fingers crossed)


"William Benson" wrote:

> How can tomwashere2 refer to Path3 before he gets to the code where it is
> assigned. Isn't it = "" in the first iteration?
>
> By the way, I love Split, I never knew about it.
>
> B.
>
> "JMB" <[email protected]> wrote in message
> news:[email protected]...
> >i think there is an issue with path3. it is the complete path right?
> > (C:\temp\xyz.xls). to close the workbook, i believe you'll need to
> > separate
> > the workbook name from the rest of the path.
> >
> > x = Split(path3, "\", -1, vbTextCompare)
> > Workbooks(x(UBound(x))).Close savechanges:=False
> >
> > Or, set an object variable = to the file you opened (lets say WkBk) and
> > use
> >
> > WkBk.Close SaveChanges:=False
> >
> > "tomwashere2" wrote:
> >
> >> I have a subroutine (macro) that automatically pulls data from various
> >> workbooks. I would like to auto close each workbook when I am done
> >> extracting the data.
> >>
> >> The vba code to do this according to Excel help is:
> >>
> >> Workbooks("BOOK1.XLS").Close SaveChanges:=False
> >>
> >> The code works when I specify the name such as "BOOK1.XLS" . The problem
> >> is
> >> that the file names vary depending on dates etc. So I assign a variable
> >> to
> >> identify the names each time it is passed through the loop and
> >> incremented.
> >> Therefore the name of each stays the same through a string variable named
> >> "path3" as follows:
> >>
> >> While LDR <= DOM
> >>
> >> Workbooks.Open Filename:=path3
> >> ' extract
> >> Selection.RemoveSubtotal
> >> Range("A2:O2").Select
> >> Range(Selection, Selection.End(xlDown)).Select
> >> Selection.Copy
> >> Windows("RwMTD.xls").Activate
> >> Sheets("MTD Data").Select
> >> Range("A1").Select
> >> Selection.End(xlDown).Select
> >> ActiveSheet.Paste
> >> Range("A1").Select
> >>
> >> ' This would be the point at which I would want to close the (Path3)
> >> file
> >>
> >> ' LDR Value
> >> LDR = LDR + 1
> >> If LDR < 0 Or LDR > 31 Then
> >> MsgBox ("Please enter a valid number for the day of month
> >> i.e.(1-31)")
> >> End
> >> Else
> >> If LDR > 0 And LDR < 10 Then
> >> LDR = "0" & LDR
> >> Else
> >> LDR = LDR
> >> End If
> >> End If
> >> path2 = "\rpt." & Year & "." & month2 & "." & LDR & ".xls"
> >> path3 = path1 & path2
> >> ' end extract
> >>
> >> Wend
>
>
>

[tomwashere2]

To JMB and Will Benson

Thank you Thank YOU!!!


This saves me from Workbook cluttering.

One Last Question for you guys:

Is there a way to auto answer to the follow up question that Excel furnishes
with each closed file. And that question is, "Would you like to save the
information on the clipboard?"...

My answer would of course be no.







"JMB" wrote:

> I figured he posted only a portion of the code and had already defined path3
> for the first iteration. He did say he tried the close method with the
> specific filename and it worked, so it must have been defined somewhere.
>
> Split and Join are nice for separating paths from filenames.
>
> On the filename issue, he could also capture the filename right after
> opening the file
>
> x = activeworkbook.name
> ..
> ..
> ..
> workbooks(x).close savechanges:=false
>
>
> Hopefully, this'll post w/o "We're sorry........" (fingers crossed)
>
>
> "William Benson" wrote:
>
> > How can tomwashere2 refer to Path3 before he gets to the code where it is
> > assigned. Isn't it = "" in the first iteration?
> >
> > By the way, I love Split, I never knew about it.
> >
> > B.
> >
> > "JMB" <[email protected]> wrote in message
> > news:[email protected]...
> > >i think there is an issue with path3. it is the complete path right?
> > > (C:\temp\xyz.xls). to close the workbook, i believe you'll need to
> > > separate
> > > the workbook name from the rest of the path.
> > >
> > > x = Split(path3, "\", -1, vbTextCompare)
> > > Workbooks(x(UBound(x))).Close savechanges:=False
> > >
> > > Or, set an object variable = to the file you opened (lets say WkBk) and
> > > use
> > >
> > > WkBk.Close SaveChanges:=False
> > >
> > > "tomwashere2" wrote:
> > >
> > >> I have a subroutine (macro) that automatically pulls data from various
> > >> workbooks. I would like to auto close each workbook when I am done
> > >> extracting the data.
> > >>
> > >> The vba code to do this according to Excel help is:
> > >>
> > >> Workbooks("BOOK1.XLS").Close SaveChanges:=False
> > >>
> > >> The code works when I specify the name such as "BOOK1.XLS" . The problem
> > >> is
> > >> that the file names vary depending on dates etc. So I assign a variable
> > >> to
> > >> identify the names each time it is passed through the loop and
> > >> incremented.
> > >> Therefore the name of each stays the same through a string variable named
> > >> "path3" as follows:
> > >>
> > >> While LDR <= DOM
> > >>
> > >> Workbooks.Open Filename:=path3
> > >> ' extract
> > >> Selection.RemoveSubtotal
> > >> Range("A2:O2").Select
> > >> Range(Selection, Selection.End(xlDown)).Select
> > >> Selection.Copy
> > >> Windows("RwMTD.xls").Activate
> > >> Sheets("MTD Data").Select
> > >> Range("A1").Select
> > >> Selection.End(xlDown).Select
> > >> ActiveSheet.Paste
> > >> Range("A1").Select
> > >>
> > >> ' This would be the point at which I would want to close the (Path3)
> > >> file
> > >>
> > >> ' LDR Value
> > >> LDR = LDR + 1
> > >> If LDR < 0 Or LDR > 31 Then
> > >> MsgBox ("Please enter a valid number for the day of month
> > >> i.e.(1-31)")
> > >> End
> > >> Else
> > >> If LDR > 0 And LDR < 10 Then
> > >> LDR = "0" & LDR
> > >> Else
> > >> LDR = LDR
> > >> End If
> > >> End If
> > >> path2 = "\rpt." & Year & "." & month2 & "." & LDR & ".xls"
> > >> path3 = path1 & path2
> > >> ' end extract
> > >>
> > >> Wend
> >
> >
> >

[JMB]

i would suggest trying the following. you can disable excels messages with

application.displayalerts = false

before your code that generates the message
and at the end of your code

application.displayalerts = true

this is also good for deleting sheets (and suppressing the "are you sure")
as well as closing a workbook and suppressing ("do you want to save").

just make sure to turn it back on if you normally place any reliance on
those safety nets



"tomwashere2" wrote:

> To JMB and Will Benson
>
> Thank you Thank YOU!!!
>
>
> This saves me from Workbook cluttering.
>
> One Last Question for you guys:
>
> Is there a way to auto answer to the follow up question that Excel furnishes
> with each closed file. And that question is, "Would you like to save the
> information on the clipboard?"...
>
> My answer would of course be no.
>
>
>
>
>
>
>
> "JMB" wrote:
>
> > I figured he posted only a portion of the code and had already defined path3
> > for the first iteration. He did say he tried the close method with the
> > specific filename and it worked, so it must have been defined somewhere.
> >
> > Split and Join are nice for separating paths from filenames.
> >
> > On the filename issue, he could also capture the filename right after
> > opening the file
> >
> > x = activeworkbook.name
> > ..
> > ..
> > ..
> > workbooks(x).close savechanges:=false
> >
> >
> > Hopefully, this'll post w/o "We're sorry........" (fingers crossed)
> >
> >
> > "William Benson" wrote:
> >
> > > How can tomwashere2 refer to Path3 before he gets to the code where it is
> > > assigned. Isn't it = "" in the first iteration?
> > >
> > > By the way, I love Split, I never knew about it.
> > >
> > > B.
> > >
> > > "JMB" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >i think there is an issue with path3. it is the complete path right?
> > > > (C:\temp\xyz.xls). to close the workbook, i believe you'll need to
> > > > separate
> > > > the workbook name from the rest of the path.
> > > >
> > > > x = Split(path3, "\", -1, vbTextCompare)
> > > > Workbooks(x(UBound(x))).Close savechanges:=False
> > > >
> > > > Or, set an object variable = to the file you opened (lets say WkBk) and
> > > > use
> > > >
> > > > WkBk.Close SaveChanges:=False
> > > >
> > > > "tomwashere2" wrote:
> > > >
> > > >> I have a subroutine (macro) that automatically pulls data from various
> > > >> workbooks. I would like to auto close each workbook when I am done
> > > >> extracting the data.
> > > >>
> > > >> The vba code to do this according to Excel help is:
> > > >>
> > > >> Workbooks("BOOK1.XLS").Close SaveChanges:=False
> > > >>
> > > >> The code works when I specify the name such as "BOOK1.XLS" . The problem
> > > >> is
> > > >> that the file names vary depending on dates etc. So I assign a variable
> > > >> to
> > > >> identify the names each time it is passed through the loop and
> > > >> incremented.
> > > >> Therefore the name of each stays the same through a string variable named
> > > >> "path3" as follows:
> > > >>
> > > >> While LDR <= DOM
> > > >>
> > > >> Workbooks.Open Filename:=path3
> > > >> ' extract
> > > >> Selection.RemoveSubtotal
> > > >> Range("A2:O2").Select
> > > >> Range(Selection, Selection.End(xlDown)).Select
> > > >> Selection.Copy
> > > >> Windows("RwMTD.xls").Activate
> > > >> Sheets("MTD Data").Select
> > > >> Range("A1").Select
> > > >> Selection.End(xlDown).Select
> > > >> ActiveSheet.Paste
> > > >> Range("A1").Select
> > > >>
> > > >> ' This would be the point at which I would want to close the (Path3)
> > > >> file
> > > >>
> > > >> ' LDR Value
> > > >> LDR = LDR + 1
> > > >> If LDR < 0 Or LDR > 31 Then
> > > >> MsgBox ("Please enter a valid number for the day of month
> > > >> i.e.(1-31)")
> > > >> End
> > > >> Else
> > > >> If LDR > 0 And LDR < 10 Then
> > > >> LDR = "0" & LDR
> > > >> Else
> > > >> LDR = LDR
> > > >> End If
> > > >> End If
> > > >> path2 = "\rpt." & Year & "." & month2 & "." & LDR & ".xls"
> > > >> path3 = path1 & path2
> > > >> ' end extract
> > > >>
> > > >> Wend
> > >
> > >
> > >

[roumi]

Have a similar problem and will appreciate if you post the complete code

[JMB]

for the object variable? right after you open a workbook, it becomes the
activeworkbook.

Const Path As String = <your path>
Dim WkBk As Workbook

< your code >

Workbooks.Open Filename:=Path
Set WkBk = ActiveWorkbook

<your code>

WkBk.Close SaveChanges:=False (or True)





"roumi" wrote:

>
> Have a similar problem and will appreciate if you post the complete code
>
>
> --
> roumi
> ------------------------------------------------------------------------
> roumi's Profile: http://www.excelforum.com/member.php...o&userid=24140
> View this thread: http://www.excelforum.com/showthread...hreadid=377308
>
>

[tomwashere2]

Thanks again.

I actually used your suggestions for Alerts = False
and I also split the filepath name string from the directory part of the
pathname in order to be able to close the variable file name.

These were useful suggestions that will save me from workbook cluttering
from the various files that I pull.

Below is a copy of the VBA code for those who had requested it:



Sub MTDMcr()
'
' MTDMcr Macro
' Macro recorded 4/5/2005 by *****'



Dim userNT, path1, path2, path3, month3, month2, LDR As String
Dim Year, month, DOM, Cbreak As Integer

userNT = InputBox("Enter your Windows NT username", "")
Year = InputBox("Enter the 4 digit year as integer", "")
month = InputBox("Enter the month as integer", "")
DOM = InputBox("MTD to run for what day of the month?", "")
LDR = InputBox("For what day did you run the report last? If you had not
run it for this month, enter 0", "")
Cbreak = 0

Select Case month
Case Is = 1
month2 = "01"
month3 = "January"
Case Is = 2
month2 = "02"
month3 = "February"
Case Is = 3
month2 = "03"
month3 = "March"
Case Is = 4
month2 = "04"
month3 = "April"
Case Is = 5
month2 = "05"
month3 = "May"
Case Is = 6
month2 = "06"
month3 = "June"
Case Is = 7
month2 = "07"
month3 = "July"
Case Is = 8
month2 = "08"
month3 = "August"
Case Is = 9
month2 = "09"
month3 = "September"
Case Is = 10
month2 = "10"
month3 = "October"
Case Is = 11
month2 = "11"
month3 = "November"
Case Is = 12
month2 = "12"
month3 = "December"
Case Else ' For Invalid Entries
MsgBox ("Please enter a valid month no. (1 - 12)")
End ' End Program
End Select

' LDR Value
If LDR = 31 Then
LDR = LDR
Else
LDR = LDR + 1
End If


If LDR < 0 Or LDR > 31 Then
MsgBox ("Please enter a valid number for the day of month i.e.(1-31)")
End
Else
If LDR > 0 And LDR < 10 Then
LDR = "0" & LDR
Else
LDR = LDR
End If
End If



path1 = "C:\Documents and Settings\" & userNT & "\My Documents\Adherence
Rpts\" & month3 & "\"
path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2



While Cbreak < 2 And LDR <= DOM

If LDR = 31 Then
Cbreak = Cbreak + 1 ' Allow to run day 31 once
End If
' extract
Workbooks.Open Filename:=path3
Selection.RemoveSubtotal
Range("A2:O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("RwMTD.xls").Activate
Sheets("MTD Data").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveSheet.Paste
Range("A1").Select

'remember to close each one after use
Application.DisplayAlerts = False
Workbooks(path2).Close SaveChanges:=False
Application.DisplayAlerts = False

If LDR = 31 Then
LDR = LDR
Else
LDR = LDR + 1 ' LDR Value increment
End If
If LDR = 31 Then
Cbreak = Cbreak + 1 ' Break control
End If

If LDR > 0 And LDR < 10 Then 'LDR format control
LDR = "0" & LDR
Else
LDR = LDR
End If

path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2
' end extract
Wend

' Remove Sentinel from RwMTD2 file and redo Subtotals for MTD
Sheets("Subtotals").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("MTD Data").Select
Cells.Select
Selection.Copy
Sheets("Subtotals").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("a1").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9,
10, _
11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1").Select


MsgBox ("Same this file using the same name. Close all others without saving")
End Sub

[roumi]

Thank you!!!

[JMB]

you're welcome. i'm glad it helped.


"tomwashere2" wrote:

> Thanks again.
>
> I actually used your suggestions for Alerts = False
> and I also split the filepath name string from the directory part of the
> pathname in order to be able to close the variable file name.
>
> These were useful suggestions that will save me from workbook cluttering
> from the various files that I pull.
>
> Below is a copy of the VBA code for those who had requested it:
>
>
>
> Sub MTDMcr()
> '
> ' MTDMcr Macro
> ' Macro recorded 4/5/2005 by *****'
>
>
>
> Dim userNT, path1, path2, path3, month3, month2, LDR As String
> Dim Year, month, DOM, Cbreak As Integer
>
> userNT = InputBox("Enter your Windows NT username", "")
> Year = InputBox("Enter the 4 digit year as integer", "")
> month = InputBox("Enter the month as integer", "")
> DOM = InputBox("MTD to run for what day of the month?", "")
> LDR = InputBox("For what day did you run the report last? If you had not
> run it for this month, enter 0", "")
> Cbreak = 0
>
> Select Case month
> Case Is = 1
> month2 = "01"
> month3 = "January"
> Case Is = 2
> month2 = "02"
> month3 = "February"
> Case Is = 3
> month2 = "03"
> month3 = "March"
> Case Is = 4
> month2 = "04"
> month3 = "April"
> Case Is = 5
> month2 = "05"
> month3 = "May"
> Case Is = 6
> month2 = "06"
> month3 = "June"
> Case Is = 7
> month2 = "07"
> month3 = "July"
> Case Is = 8
> month2 = "08"
> month3 = "August"
> Case Is = 9
> month2 = "09"
> month3 = "September"
> Case Is = 10
> month2 = "10"
> month3 = "October"
> Case Is = 11
> month2 = "11"
> month3 = "November"
> Case Is = 12
> month2 = "12"
> month3 = "December"
> Case Else ' For Invalid Entries
> MsgBox ("Please enter a valid month no. (1 - 12)")
> End ' End Program
> End Select
>
> ' LDR Value
> If LDR = 31 Then
> LDR = LDR
> Else
> LDR = LDR + 1
> End If
>
>
> If LDR < 0 Or LDR > 31 Then
> MsgBox ("Please enter a valid number for the day of month i.e.(1-31)")
> End
> Else
> If LDR > 0 And LDR < 10 Then
> LDR = "0" & LDR
> Else
> LDR = LDR
> End If
> End If
>
>
>
> path1 = "C:\Documents and Settings\" & userNT & "\My Documents\Adherence
> Rpts\" & month3 & "\"
> path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
> path3 = path1 & path2
>
>
>
> While Cbreak < 2 And LDR <= DOM
>
> If LDR = 31 Then
> Cbreak = Cbreak + 1 ' Allow to run day 31 once
> End If
> ' extract
> Workbooks.Open Filename:=path3
> Selection.RemoveSubtotal
> Range("A2:O2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Copy
> Windows("RwMTD.xls").Activate
> Sheets("MTD Data").Select
> Range("A1").Select
> Selection.End(xlDown).Select
> ActiveSheet.Paste
> Range("A1").Select
>
> 'remember to close each one after use
> Application.DisplayAlerts = False
> Workbooks(path2).Close SaveChanges:=False
> Application.DisplayAlerts = False
>
> If LDR = 31 Then
> LDR = LDR
> Else
> LDR = LDR + 1 ' LDR Value increment
> End If
> If LDR = 31 Then
> Cbreak = Cbreak + 1 ' Break control
> End If
>
> If LDR > 0 And LDR < 10 Then 'LDR format control
> LDR = "0" & LDR
> Else
> LDR = LDR
> End If
>
> path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
> path3 = path1 & path2
> ' end extract
> Wend
>
> ' Remove Sentinel from RwMTD2 file and redo Subtotals for MTD
> Sheets("Subtotals").Select
> Cells.Select
> Selection.Delete Shift:=xlUp
> Range("A1").Select
> Sheets("MTD Data").Select
> Cells.Select
> Selection.Copy
> Sheets("Subtotals").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
> Key2:=Range("C2") _
> , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
> False, Orientation:=xlTopToBottom
> Range("a1").Select
> Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9,
> 10, _
> 11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False,
> SummaryBelowData:=True
> ActiveSheet.Outline.ShowLevels RowLevels:=2
> Range("A1").Select
>
>
> MsgBox ("Same this file using the same name. Close all others without saving")
> End Sub
>
>
>
>
>
>
>
>
>