#1
|
|||
|
|||
VBA Not working
Hi
I have adapted this macro from various threads, however, it runs, but does not save as expected, only one sheet saves, not always the same sheet, and not always the correct filename In short, 12 sheets in one workbook, run macro to move and save in folder on desktop, Filename is from cells A2, and C2 and L2 so one save file should be Acumen week 47 Week 48 A2 - Acumen, C2 Week 47, L2 Week 48 Any help, direction, would be appreciated Regards Brian Code:
Dim part1 As String Dim part2 As String Dim part3 As String 'setting out the cells part1 = Range("A2").Value part2 = Range("C2").Value part3 = Range("L2").Value Dim strUser As String strUser = Environ("UserName") Application.DisplayAlerts = False Sheets("Acumen").Select Sheets("Acumen").Move ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close Sheets("Carlson").Select Sheets("Carlson").Move ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close Sheets("Gefco").Select Sheets("Gefco").Move ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close Last edited by Pecoflyer; 05-11-2019 at 11:42 PM. |
#2
|
||||
|
||||
a guess, and untested:
Code:
Sub blah() Dim part1 As String Dim part2 As String Dim part3 As String 'setting out the cells Dim strUser As String strUser = Environ("UserName") Application.DisplayAlerts = False For Each sht In Sheets(Array("Acumen", "Carlson", "Gefco")) With sht part1 = .Range("A2").Value part2 = .Range("C2").Value part3 = .Range("L2").Value .Move End With ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close Next sht Application.DisplayAlerts = True End Sub |
#3
|
||||
|
||||
@Brian
Hi and welcome in the future please wrap code with code tags ( select code and click the #button) I will do it for you this time Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
Hi P45CAL
The seems to be an error on the filename when saving, I get an error in yellow ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close I changed the file name to exclude " & part1 & " " & part2 & " " & part3 & ".xlsx", and just save to desktop inthe folder, and it worked fine Any ideas why this is not working - saving filename as cell A2 & C2 & L2 Pecoflyer - unclear what I have not done when entering the thread ? Regards Brian |
#5
|
||||
|
||||
While in debug mode enter this into the Immediate pane (Ctrl+G if you can't see it):
Code:
?"C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx" Examine the results and see if there's anything obvious: Does the file already exisit? Is the path and filename 100% correct? Does the folder exist? Are there any illegal characters in the name? What is the error message? |
#6
|
||||
|
||||
Quote:
As I mentioned you did not wrap your code with code tags
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
Hi p45cal
Thank you - works perfectly. I did have an illegal characters in the filename Incidentally, if I wanted to save as a PDF File, instead of workbook what do I have t amend the format to FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Regards Brian |
#8
|
||||
|
||||
try changing the line which starts:
Code:
ActiveWorkbook.SaveAs… Code:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Tasks Switch to Non-Working Days on Working Calendar | danelloc | Project | 3 | 10-02-2017 12:38 PM |
TOC Not Working | Gary91 | Word | 3 | 01-21-2015 05:52 AM |
working 4 10's, need help to set up | rkirkland | Project | 1 | 12-19-2012 02:47 PM |
GIF's stop working | Kwarior | PowerPoint | 3 | 03-17-2010 09:59 PM |
Spellchecker not working | GR8Fandini | Word | 6 | 02-07-2010 01:12 PM |