Posts by balla506

balla506 · Mar 14th 2016

Re: Saving with VBA

I have done this and the path is correct. I literally just change xlsm to xls and it saves fine. As soon as I add the xlsm is when it fails. i have scoured forums to no avail. I have no idea what would cause this.

balla506 · Mar 14th 2016

Re: Saving with VBA

I tried this but am still getting the same error. It works however if I change to .xls. I do not understand why .xlsm will not work.

balla506 · Mar 11th 2016

I am trying to just have this save a file with a given name. I keep getting a "Method 'SaveAs' of object'_Workbook' failed error". If I change ".xlsm" to ".xls" it will save in the right path but as soon as I add the m I get an error. Any thoughts?

Code

Sub save()


Dim path As String
Dim filename As String
Dim d As String
Dim b As String
Dim f As String
Dim a As String
Dim iyear As String








iyear = Year(Date)
d = DateAdd("m", -1, Now)
b = Month(d)
f = MonthName(b)
a = Format(d, "mm") & ". " & f & " " & iyear
filename = "RFM" & f & " " & iyear & ".xlsm"




   ActiveWorkbook.SaveAs filename:="\\office\Group11\T&A\Data Specialist\RFT REPORT\Source Reports\" & iyear & "\" & a & "\" & filename, _
   FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
   ReadOnlyRecommended:=False, CreateBackup:=False


End Sub

Display More

balla506 · Jun 13th 2013

Re: Create Subfiles from a master file using key to split workbooks to seperate files

FYI-I've posted this on a couple of different forums here
http://www.mrexcel.com/forum/e…books-seperate-files.html

http://www.vbaexpress.com/forum/search.php?searchid=406875

http://www.ozgrid.com/forum/showthread.php?t=179507 - orignal posting forum with no responses

http://www.excelguru.ca/forums…rkbooks-to-seperate-files

balla506 · Jun 13th 2013

Re: Create Subfiles from a master file using key to split workbooks to seperate files

Please help..

balla506 · Jun 12th 2013

Hi,

I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet. Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location. Any help on this is much appreciated. Thanks.

Code

Sub Create_Subfiles()




    Dim FDMName           As String
    Dim FBName           As String
    Dim DIYName           As String
    Dim WMName           As String
    Dim FPath           As String
    Dim BWName          As String
    
    'File names and directory path
    FDMName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f14").Value
    FBName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f13").Value
    DIYName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f15").Value
    WMName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f12").Value
    TOTName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f16").Value
    FPath = "C:/desktop"
   
    
    
    
    
    
    




    
    
    
    'open template files
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\1.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\2.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\3.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\4.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\Total.xlsm"
    Windows("Sales Forecast Template.xlsm").Activate




    'move worksheets to proper workbooks and save them to correct directory
    Sheets(Array("Sheet1", "Sheet 2").Sheets(1)
    Sheets(Array("Hierarchy", "Couponing", "Sheet1")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.Visible = False
    Range("A2").Select
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FDMName, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close False
    Windows("Sales Forecast Template.xlsm").Activate
    
    Sheets(Array("Sheet 5", "Sheet 7").Sheets(1)
    Sheets(Array("Hierarchy", "Couponing", "Sheet1")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.Visible = False
    Range("A2").Select
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FBName, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close False
    Windows("Sales Forecast Template.xlsm").Activate
















ThisWorkbook.Close False
End Sub

Display More

balla506 · Jun 6th 2013

Re: paste values

Thanks for your help on this. Just what I needed.

balla506 · Jun 5th 2013

Hi,

I am having problems with this simple macro. All I am trying to do is select multiple ranges and then paste values for those ranges to get rid of the formulas. When I run this for some reason e1 is getting the value from A1. Thanks in advance for your help.

Code

Sub Test()
 
 Range("a1:c1, e1").Select
Selection.Value = Selection.Value




 End Sub