Unfortunately, it still bugged out on the saveAs line.
Posts by CodingNewb
-
-
Display More
I have list of Expenses in Sheet2
Sheet1 contains the detailed expenses of each item
In Sheet3 in column B, When I select the list of expenses ( Already Done )
Then ( What I Want to Do is )
Column C should show only the expenses related to items selected in Column B, The list of Expenses is shown in Sheet1
Have you tried Power Query? That might work for this.
-
Thanks, Rory! Where do I put it in the SaveAs line? I put it after SaveAs, but it errored out.
-
Display More
Well, don't ask another question. Continue with the original.
That comment does nothing to help solve your problem.
What error message did you get?
What didn't work?
Respond to this in the original question and attach your workbook with the code in that doesn't work.
I don't get an error message. It just still saves to random folders. Just used the code below and it saved to the Documents folder on my computer, not the folder the file is in.
Code
Display MoreSub SaveAsFile_2() Dim WBPath As String, WBName As String, CustomerName As String, PricingAnalysis As String, newFullPath As String ' SaveAsFile Macro WBPath = ThisWorkbook.Path WBName = Replace(ThisWorkbook.Name, ".xlsm", "") CustomerName = ActiveSheet.Range("B3").Text PricingAnalysis = ActiveSheet.Range("A1").Text 'File Formats: '51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx) '52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm) '50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb) '56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls) newFullPath = WBPath & "\" & WBName ThisWorkbook.SaveAs fileName:=CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm" Range("B27:G40").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=17 Range("B49:G62").Select Selection.ClearContents Range("A1").Select End Sub -
It still saves in random files that I previously did a save as to.
-
Sorry. It didn't work when I tried it.
-
Thanks! So what would that look like? I thought the newFullpath was specifying where to save it.
-
Sometimes my macro saves correctly, and other times, they save to a different file. Any idea why these are sending documents to the incorrect folder sometimes and the correct one other times? I'm just trying to save the workbook in the same folder that it is already in and add the date at the end - one in PDF format and the other in excel format.
Thank y ou!
Code
Display MoreSub SaveAsPDF() 'THIS ONE WORKS ' SaveAsPDF Macro WBPath = ActiveWorkbook.path WBName = Replace(ActiveWorkbook.Name, ".xlsm", "") CustomerName = ActiveSheet.Range("C1").Text PricingAnalysis = ActiveSheet.Range("C2").Text currentDate = Format(Date, "MM.DD.YYYY") ThisWorkbook.Sheets(Array("E_Cover Page", "E_Pricing Analysis (FP)")).Select newFullPath = WBPath & "\" & WBName & " " & currentDate & ".PDF" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=CustomerName & "_" & PricingAnalysis & "_" & currentDate & ".PDF" 'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False End Sub Sub SaveAsFile() 'THIS ONE WORKS ' SaveAsFile Macro WBPath = ActiveWorkbook.path WBName = Replace(ActiveWorkbook.Name, ".xlsm", "") CustomerName = ActiveSheet.Range("B3").Text PricingAnalysis = ActiveSheet.Range("A1").Text currentDate = Format(Date, "MM.DD.YYYY") newFullPath = WBPath & "\" & WBName & " " & currentDate & ".xlsm" ActiveWorkbook.SaveAs fileName:=CustomerName & "_" & PricingAnalysis & "_" & currentDate & ".xlsm" 'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False End Sub -
Cell has =+IF(A21<=$A$20,VLOOKUP(A21,Inputs!$N$17:$O$26,2,FALSE),"") in it; however, when the cell is false and turns out "", which is nothing in the cell, how do I hide/delete the rows?
Code
Display MoreSub Delete_Blank_Rows() On Error Resume Next With Range("B20:B29") .Value = .Value .SpecialCells(xlCellTypeBlanks).EntierRow.Delete End With End Sub **Code 2 I tried** Sub E_Hide_Rows() Worksheets("Zone Pricing").Select Range("$B$20:$B$29").AutoFilter 1, "<>", , , False Worksheets("Zone Pricing").Select Range("$I$34:$I$43").AutoFilter 1, "<>", , , False End Sub -
2 Macro Issues
Sub SaveAsFile: I have the same macro for a PDF conversion, and it works. For whatever reason, when I use the below, it doesn't save the excel macro enabled workbook to the same path the file is in. Once I get the SaveAsFile done, I want to add another macro at the bottom that saves the excel in the same location with the current date at the end upon opening.
Thanks!
Code
Display MoreSub SaveAsFile() ' ' SaveAsFile Macro WBPath = ActiveWorkbook.Path WBName = Replace(ActiveWorkbook.Name, ".xlsm", "") CustomerName = ActiveSheet.Range("B1").Text PricingAnalysis = ActiveSheet.Range("B3").Text currentDate = Format(Date, "MM.DD.YYYY") newFullPath = WBPath & "\" & WBName & " " & currentDate & ".xlsm" ActiveWorkbook.SaveAs Filename:=CustomerName & "_" & PricingAnalysis & "_" & currentDate & ".xlsm" 'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False End Sub **Commented out as of now because the top macro won't work correctly** 'Private Sub Workbook_Open() 'Call SaveAsFile 'End Sub