Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 09-08-2018, 06:02 AM
NoSparks NoSparks is offline Entering Formula in VB Windows 7 64bit Entering Formula in VB Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
If I use the commented out line above, file is saved correctly.
don't know about the variables, but the "\QBOUploads\" part differs
Reply With Quote
  #17  
Old 09-08-2018, 06:07 AM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by NoSparks View Post
don't know about the variables, but the "\QBOUploads\" part differs
Well spotted! There's a space in one, not in the other.
Reply With Quote
  #18  
Old 09-09-2018, 03:44 PM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

Thanks. I tried this too. Still don't see anything in the immediate window. I have copied the entire code below if this helps:

Code:
Sub CopyWorksheet()
    
    Dim MyFileName As String
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet
    Dim aThisFilename As String
    Dim CurrentWB As Workbook

    Set CurrentWB = ActiveWorkbook
    

    'If MsgBox("Copy specific sheet to a new workbook" & vbCr & _
    '"New sheet will be pasted as values, named ranges removed" _
    , vbYesNo, "NewCopy") = vbNo Then Exit Sub

    With Application
        .ScreenUpdating = False

         '       Copy specific sheets
         '       *SET THE SHEET NAMES TO COPY BELOW*
         '       Array("Sheet Name", "Another sheet name", "And Another"))
         '       Sheet names go inside quotes, seperated by commas
        On Error GoTo ErrCatcher
        Sheets(Array("Sheet1")).Copy
        On Error GoTo 0

         '       Paste sheets as values
         '       Remove External Links, Hperlinks and hard-code formulas
         '       Make sure A1 is selected on all sheets
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select

         '       Remove named ranges
        For Each nm In ActiveWorkbook.Names
            nm.Delete
        Next nm

         '       Input box to name new file
        'NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

         '       Save it with the NewName and in the same directory as original
        'ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsx"
        'ActiveWorkbook.Close SaveChanges:=False
    
    'MyFileName = CurrentWB.Path & "\QBO Uploads\" & "Toongabbie Roster - " & Format(Now(), "dd-mm-yyyy") & ".csv"
    MyFileName = CurrentWB.Path & "\QBOUploads\" & Sheets("Analysis").Range("I1").Value & Format(Now(), "dd-mm-yyyy") & ".csv"
    
    MsgBox MyFileName
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True

    MsgBox "CSV file has been created "
    'ActiveWindow.Close
    
    
        .ScreenUpdating = True
    End With
    Exit Sub

ErrCatcher:
    MsgBox "Specified sheet does not exist within this workbook"

End Sub
Attached Files
File Type: pdf error.pdf (179.4 KB, 8 views)
Reply With Quote
  #19  
Old 09-10-2018, 01:28 AM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

The yellow highlighting in your picture in the .pdf file you attached is on a line which either had an error occur on it, or you stepped to it using F8 on the keyboard.
Either way, it hasn't been executed yet, so MyFilename could well be empty at this point.
The code hasn't even reached the break point a line below.
What happens if you press F8 a couple of times? Does a message box pop up? If so, does the file name look right?


You still don't seem to have addressed the difference between the active line and the line above:
Reply With Quote
  #20  
Old 09-24-2018, 10:32 AM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

One can only guess if this has been sorted or not.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Entering Formula in VB Entering a column value in a cell, to be used as part of a reference in a formula in another cell paulkaye Excel 4 02-26-2017 04:18 AM
entering name in outlook does not autofill Franksplace2 Word 0 03-08-2015 04:10 PM
Entering Formula in VB entering duration in months ketanco Project 1 12-17-2014 01:09 PM
Entering Formula in VB entering lump sum costs ketanco Project 1 12-24-2011 12:56 PM
Entering Contact details GemmaD Outlook 0 12-18-2008 06:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:40 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft