Close workbook

  • Thread starter Thread starter Oldjay
  • Start date Start date
O

Oldjay

I want tp close tempdata if it is open

If ActiveWorkbook = "Tempdata.xls" Then
ActiveWorkbook.Close
End If

This dosn't work
 
Thiat worked but I didn't tell you everything.

Sometimes Tempdata .xlx is open but not the active workbook
 
Thiat worked but I didn't tell you everything.

Sometimes Tempdata .xlx is open but not the active workbook
Click to expand...

Oldjay,

This should do the trick:

Sub CloseTempData()
Dim wkb As Workbook

For Each wkb In Workbooks
If wkb.Name = "Tempdata" Then
wkb.Close
End If
Next wkb

End Sub

Alex
 
That didn't work. It steped thru if but didn't see Tempdata. I even tried to
add .xls just in case that worked. Maybe there is a better way to do what I
want. Why I am checking to see if Tempdata was left open is because I need to
copy data to it then save it under a other name.
Here is the first part of the sub If Tempdata is open then you can't save it
because another workbook is all ready open and the sub fails

Sub SaveNewQuot() ' Saves input data for new quote

Application.ScreenUpdating = False 'Keeps screen from updating thereby
speeding routine
Application.DisplayAlerts = False 'Suppresses normal alerts

Dim Quote1 As String
Dim quotenumber1 As String
Dim numbersave As String
Dim QuoteRecords As String
Dim username As String

username = Application.username

numbersave = Range("C4") 'Save Estimate # to the variable "numbersave"

Range("I190:J191").Select ' Reset FOB and origin
Selection.Copy
Range("B190").Select
Selection.PasteSpecial Paste:=xlPasteFormulas

For Each wkb In Workbooks
If wkb.Name = "Tempdata" Then
wkb.Close
End If
Next wkb

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="TempData.xls"

Windows("Master5.xls").Activate 'Customer info & part description
Range("C4:C34").Select
Selection.Copy
Windows("TempData.XLS").Activate
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
 
That didn't work. It steped thru if but didn't see Tempdata. I even tried to
add .xls just in case that worked. Maybe there is a better way to do what I
want. Why I am checking to see if Tempdata was left open is because I needto
copy data to it then save it under a other name.
Here is the first part of the sub If Tempdata is open then you can't save it
because another workbook is all ready open and the sub fails

Sub SaveNewQuot() ' Saves input data for new quote

    Application.ScreenUpdating = False 'Keeps screen from updating thereby
speeding routine
    Application.DisplayAlerts = False  'Suppresses normal alerts

    Dim Quote1 As String
    Dim quotenumber1 As String
    Dim numbersave As String
    Dim QuoteRecords As String
    Dim username As String

    username = Application.username

        numbersave = Range("C4") 'Save Estimate # to the variable "numbersave"

        Range("I190:J191").Select ' Reset FOB and origin
        Selection.Copy
        Range("B190").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas

        For Each wkb In Workbooks
           If wkb.Name = "Tempdata" Then
              wkb.Close
           End If
        Next wkb

        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:="TempData.xls"

        Windows("Master5.xls").Activate 'Customer info & part description
        Range("C4:C34").Select
        Selection.Copy
        Windows("TempData.XLS").Activate
        Range("C4").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas
Click to expand...

Sorry, yes my previous post should read If wkb.Name = "Tempdata.xls",
which works when I tested it.
 
Sorry, yes my previous post should read If wkb.Name = "Tempdata.xls",
which works when I tested it.
Click to expand...

And also, what are you trying to do with the numbersave variable? Your
code is confusing here - perhaps it should be:

Let numbersave = Range("C4").Value

I'm also not sure exactly what you're trying to achieve - it's
difficult to understand without your full code there.
 
numbersave = Range("C4") 'Save Estimate # to the variable "numbersave"

After copying and pasting about 50 variables the fileis saved

quotenumber1 = InputBox("Please enter QUOTE file name to save to Server3", _
"The AuldCompany", "\\server3\jobs\estimate1\New_Quot1\" & numbersave)
Quote1 = quotenumber1 & ".XLS"
ActiveWorkbook.SaveAs Filename:=Quote1
ActiveWorkbook.Close

I know this is not good code but I have use it for a long time and I knew
that I couldn't have Tempdata.xls open to save a quote.
I am now trying to teach some one else to use this workbook and I am trying
to clean up this 3.5Meg 15 year old spreadsheet.
 
Back
Top