+ Reply to Thread
Results 1 to 19 of 19

save first sheet only

Hybrid View

stevesunfold save first sheet only 12-14-2008, 11:01 AM
Simon Lloyd Ive not looked at your code... 12-14-2008, 11:47 AM
stevesunfold hi simon really appreciate... 12-14-2008, 11:57 AM
davesexcel Did you try his code? 12-14-2008, 12:00 PM
stevesunfold im not sure where to put it... 12-14-2008, 12:38 PM
stevesunfold if anyone could help i really... 12-14-2008, 02:59 PM
stevesunfold i have just tried simons code... 12-14-2008, 03:55 PM
stevesunfold im getting paranoid now lol... 12-14-2008, 06:18 PM
Simon Lloyd Maybe i will take a look at... 12-15-2008, 04:07 PM
stevesunfold thanks simon much appreciated 12-16-2008, 09:01 AM
gummi Do these changes, and try...... 12-16-2008, 09:23 AM
stevesunfold cheeers for the effort gummi ... 12-16-2008, 01:26 PM
stevesunfold bump bump bump 12-17-2008, 03:00 PM
stevesunfold bump bump bump 12-18-2008, 01:02 PM
  1. #1
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690

    save first sheet only

    hi guys sorry to bug you all again but i really need to get this done for my boss and im at my wits end
    i have tried to change this code to save just the first sheet
    and was wondering if anyone could hel me fulfil this as i keep getting an error
    i will make the code red as to what i have changed

    Sub COPYandPRINT()
    Sheet1.Unprotect Password:="contracts"
         Application.ScreenUpdating = False
         Application.DisplayAlerts = False
         ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=False
         
         With Sheets("PURCHASE ORDER NUMBERS")
           R = .Cells(Rows.Count, "A").End(xlUp).Row + 1
           .Cells(R, "A") = PONUMBER
           .Cells(R, "B") = SUPPLIER
           .Cells(R, "C") = xDATE
           .Cells(R, "D") = JOBNUMBER
           .Cells(R, "E") = CUSTOMERNAME
           .Cells(R, "F") = DESCRIPTION
         End With
         
         Application.ScreenUpdating = True
         Application.DisplayAlerts = False
        
         SaveJobFile
         
    End Sub
    
    Sub SaveJobFile()
     'declare all variables
      Dim stOfPath As String
      Dim ActualMidFolder As String
      Dim EndOfNameAndPath As String
      Dim FileNameToSave As String
      Dim JobNum As String
      Dim PONum As String
      Dim SuppliersName As String
    
      'define initial variables (note: the ranges may need to be changed
        JobNum = Range("G23").Value
        PONum = Worksheets("Purchase Order").Range("$K$12").Text
        SuppliersName = Range("B16")
        EndOfNameAndPath = SuppliersName & " Purchase Order " & PONum & Format(Date, " dd.mm.yy") & ".xls"
    
      'test to see if it is stock or PO (?) & define the saving path accordingly
        Select Case UCase(Left(JobNum, 1)) = "J"
            Case True
      'Rob's test path:     stOfPath = "C:\Documents and Settings\HP_Owner\My Documents\"
                stOfPath = Range("B69") & "\"
                'test for any likely folders
                    If Not (DoesFileFolderExist(stOfPath & JobNum & "*")) Then GoTo TheEnd
                'identify the exact folder
                    ActualMidFolder = GetActualFolderName(stOfPath, JobNum) & "\"
                'define rest of file name
                    EndOfNameAndPath = "Docs\" & EndOfNameAndPath
                    FileNameToSave = stOfPath & ActualMidFolder & EndOfNameAndPath
            Case False
                stOfPath = Range("B70") & "\"
                If Not (DoesFileFolderExist(stOfPath)) Then GoTo TheEnd
                    FileNameToSave = stOfPath & EndOfNameAndPath
        End
    
    
      'save the file & finish macro
        SelectSheet1.Copy    ActiveWorkbook.SaveAs Filename:=FileNameToSave
        ActiveWorkbook.Close True
        
         With Worksheets("purchase order")
           .Range("B16:F19,K17:L20,B23:C24,C29:C57,G23:H24,I23:K24,E29:J57").ClearContents
           PONUMBER = PONUMBER + 1
           .Cells(12, "K") = PONUMBER
           .Activate
           .Range("B16").Select
           Sheet1.Protect Password:="contracts"
           
               ChDir "C:\Documents and Settings\steve taylor\Desktop"
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Documents and Settings\steve taylor\Desktop\PURCHASE ORDER DONE AND DUSTED.xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
         End With
    
        Exit Sub
    
    TheEnd:
      'warn nothing exists & end the macro
        MsgBox "Macro ending b/c no Folder with the following number exists : " & stOfPath & JobNum & "*", , "FYI"
        Debug.Print stOfPath & JobNum & "*"
    End Select
    
    
    Public Function DoesFileFolderExist(strfullpath As String) As Boolean
    'sourced from www.excelguru.ca/node/30 by Ken Puls
    'note it only checks for the existence of the lowest folder (or the file) in the strfullpath string.
    If Not Dir(strfullpath, vbDirectory) = vbNullString Then DoesFileFolderExist = True
    End Function
    
    Function GetActualFolderName(StartOfPath As String, StartOfFuzzyFolder As String)
    'sourced & modified from http://www.themssforum.com/ExcelProgramming/parent-folder/
    Dim oFSO As Object
    Dim SubDirectory
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    With oFSO.GetFolder(StartOfPath)
        For Each SubDirectory In .SubFolders
            If UCase(Left(SubDirectory.Name, Len(StartOfFuzzyFolder))) = UCase(StartOfFuzzyFolder) Then
                GetActualFolderName = SubDirectory.Name
                Exit For
            End If
        Next SubDirectory
    End With
    'free memory
    Set oFSO = Nothing
    
    End Function
    Attached Files Attached Files

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Ive not looked at your code or workbook as i dont have time at the mo but to save a sheet use:
    Sheets("Sheet1").SaveAs "MyTest" & ".xls"
    Not all forums are the same - seek and you shall find

  3. #3
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi simon
    really appreciate the reply
    but ive tried everything and obviously ont want to mess up everything
    so i would be most grateful if you could tak a look in yur own time and get back to me
    thanks so much

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525
    Quote Originally Posted by stevesunfold View Post
    hi simon
    really appreciate the reply
    but ive tried everything .........
    Did you try his code?

  5. #5
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    im not sure where to put it and what to take out dave

  6. #6
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    if anyone could help i really would appreciate it

  7. #7
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    i have just tried simons code to where i thought it should go and i get
    compile error
    expected end sub

    this is the part i changed
    'save the file & finish macro
        Sheets("Sheet1").SaveAs "MyTest" & ".xls"
        ActiveWorkbook.SaveAs Filename:=FileNameToSave
        ActiveWorkbook.Close True

  8. #8
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    im getting paranoid now lol
    surely someone understands this piece of code.
    willing to pay for help

  9. #9
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    simon ( or anyone )
    i changed the code you put to
    'save the file & finish macro
        Sheets("PURCHASE ORDER").SaveAs "MyTest" & ".xls"
        ActiveWorkbook.SaveAs Filename:=FileNameToSave
        ActiveWorkbook.Close True
    as sheet 1 is actually caled PURCHASE ORDER but it still comes back
    with the same problem

  10. #10
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Maybe i will take a look at your workbook tomorrow

  11. #11
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    thanks simon
    much appreciated

  12. #12
    Registered User
    Join Date
    06-16-2008
    Posts
    32
    Quote Originally Posted by stevesunfold View Post
    hi guys sorry to bug you all again but i really need to get this done for my boss and im at my wits end
    i have tried to change this code to save just the first sheet
    and was wondering if anyone could hel me fulfil this as i keep getting an error
    i will make the code red as to what i have changed

    Sub COPYandPRINT()
    Sheet1.Unprotect Password:="contracts"
         Application.ScreenUpdating = False
         Application.DisplayAlerts = False
         ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=False
         
         With Sheets("PURCHASE ORDER NUMBERS")
           R = .Cells(Rows.Count, "A").End(xlUp).Row + 1
           .Cells(R, "A") = PONUMBER
           .Cells(R, "B") = SUPPLIER
           .Cells(R, "C") = xDATE
           .Cells(R, "D") = JOBNUMBER
           .Cells(R, "E") = CUSTOMERNAME
           .Cells(R, "F") = DESCRIPTION
         End With
         
         Application.ScreenUpdating = True
         Application.DisplayAlerts = False
        
         SaveJobFile
         
    End Sub
    
    Sub SaveJobFile()
     'declare all variables
      Dim stOfPath As String
      Dim ActualMidFolder As String
      Dim EndOfNameAndPath As String
      Dim FileNameToSave As String
      Dim JobNum As String
      Dim PONum As String
      Dim SuppliersName As String
    
      'define initial variables (note: the ranges may need to be changed
        JobNum = Range("G23").Value
        PONum = Worksheets("Purchase Order").Range("$K$12").Text
        SuppliersName = Range("B16")
        EndOfNameAndPath = SuppliersName & " Purchase Order " & PONum & Format(Date, " dd.mm.yy") & ".xls"
    
      'test to see if it is stock or PO (?) & define the saving path accordingly
        Select Case UCase(Left(JobNum, 1)) = "J"
            Case True
      'Rob's test path:     stOfPath = "C:\Documents and Settings\HP_Owner\My Documents\"
                stOfPath = Range("B69") & "\"
                'test for any likely folders
                    If Not (DoesFileFolderExist(stOfPath & JobNum & "*")) Then GoTo TheEnd
                'identify the exact folder
                    ActualMidFolder = GetActualFolderName(stOfPath, JobNum) & "\"
                'define rest of file name
                    EndOfNameAndPath = "Docs\" & EndOfNameAndPath
                    FileNameToSave = stOfPath & ActualMidFolder & EndOfNameAndPath
            Case False
                stOfPath = Range("B70") & "\"
                If Not (DoesFileFolderExist(stOfPath)) Then GoTo TheEnd
                    FileNameToSave = stOfPath & EndOfNameAndPath
        End
    
    
      'save the file & finish macro
       Sheets("PURCHASE ORDER").SaveAs Filename:=FileNameToSave 
        ActiveWorkbook.Close True
        
         With Worksheets("purchase order")
           .Range("B16:F19,K17:L20,B23:C24,C29:C57,G23:H24,I23:K24,E29:J57").ClearContents
           PONUMBER = PONUMBER + 1
           .Cells(12, "K") = PONUMBER
           .Activate
           .Range("B16").Select
           Sheet1.Protect Password:="contracts"
           
               ChDir "C:\Documents and Settings\steve taylor\Desktop"
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Documents and Settings\steve taylor\Desktop\PURCHASE ORDER DONE AND DUSTED.xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
         End With
    
        Exit Sub
    
    TheEnd:
      'warn nothing exists & end the macro
        MsgBox "Macro ending b/c no Folder with the following number exists : " & stOfPath & JobNum & "*", , "FYI"
        Debug.Print stOfPath & JobNum & "*"
    End Select
    
    End Sub
    Public Function DoesFileFolderExist(strfullpath As String) As Boolean
    'sourced from www.excelguru.ca/node/30 by Ken Puls
    'note it only checks for the existence of the lowest folder (or the file) in the strfullpath string.
    If Not Dir(strfullpath, vbDirectory) = vbNullString Then DoesFileFolderExist = True
    End Function
    
    Function GetActualFolderName(StartOfPath As String, StartOfFuzzyFolder As String)
    'sourced & modified from http://www.themssforum.com/ExcelProgramming/parent-folder/
    Dim oFSO As Object
    Dim SubDirectory
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    With oFSO.GetFolder(StartOfPath)
        For Each SubDirectory In .SubFolders
            If UCase(Left(SubDirectory.Name, Len(StartOfFuzzyFolder))) = UCase(StartOfFuzzyFolder) Then
                GetActualFolderName = SubDirectory.Name
                Exit For
            End If
        Next SubDirectory
    End With
    'free memory
    Set oFSO = Nothing
    
    End Function
    Do these changes, and try... might work... or the not :P
    Last edited by gummi; 12-16-2008 at 09:25 AM.

  13. #13
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    cheeers for the effort gummi
    but that doesnt work either
    ;(

  14. #14
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    bump bump bump

  15. #15
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    bump bump bump

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1