#16
|
|||
|
|||
Quote:
|
#17
|
||||
|
||||
Well spotted! There's a space in one, not in the other.
|
#18
|
|||
|
|||
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 |
#19
|
||||
|
||||
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: |
#20
|
||||
|
||||
One can only guess if this has been sorted or not.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 duration in months | ketanco | Project | 1 | 12-17-2014 01:09 PM |
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 |