Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-11-2019, 10:39 AM
BrianT BrianT is offline VBA Not working Windows 10 VBA Not working Office 2019
Novice
VBA Not working
 
Join Date: May 2019
Posts: 3
BrianT is on a distinguished road
Default 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
Etc etc

Last edited by Pecoflyer; 05-11-2019 at 11:42 PM.
Reply With Quote
  #2  
Old 05-11-2019, 10:54 AM
p45cal's Avatar
p45cal p45cal is offline VBA Not working Windows 10 VBA Not working 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

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
Reply With Quote
  #3  
Old 05-11-2019, 11:42 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline VBA Not working Windows 7 64bit VBA Not working Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@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
Reply With Quote
  #4  
Old 05-11-2019, 11:58 PM
BrianT BrianT is offline VBA Not working Windows 10 VBA Not working Office 2019
Novice
VBA Not working
 
Join Date: May 2019
Posts: 3
BrianT is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 05-12-2019, 12:36 AM
p45cal's Avatar
p45cal p45cal is offline VBA Not working Windows 10 VBA Not working 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

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"
and press Enter.
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?
Reply With Quote
  #6  
Old 05-12-2019, 08:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline VBA Not working Windows 7 64bit VBA Not working Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Quote:
Originally Posted by BrianT View Post
Pecoflyer - unclear what I have not done when entering the thread ?

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
Reply With Quote
  #7  
Old 05-13-2019, 02:41 PM
BrianT BrianT is offline VBA Not working Windows 10 VBA Not working Office 2019
Novice
VBA Not working
 
Join Date: May 2019
Posts: 3
BrianT is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 05-14-2019, 04:32 AM
p45cal's Avatar
p45cal p45cal is offline VBA Not working Windows 10 VBA Not working 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

try changing the line which starts:
Code:
ActiveWorkbook.SaveAs…
to something along the lines of:
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
Reply With Quote
Reply

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
VBA Not working TOC Not Working Gary91 Word 3 01-21-2015 05:52 AM
VBA Not working 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:15 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