+ Reply to Thread
Results 1 to 66 of 66

Need VBA to create new Workbook and name it based on cell value in original file

  1. #1
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Need VBA to create new Workbook and name it based on cell value in original file

    Hello.

    I have two sheets attached for use as examples. I want to a macro in the "Sched_Example" file that will create a new workbook that looks exactly like the "Export Example" file (ignore formulas in that one), and then copy and past the info into the "Input" tab on the "Export_Example" file. I realize it's probably not ideal to create a macro that recreates "Export Example" each time but I'm not sure how to replicate this file most efficiently so that the data can be pulled into it.

    Any help is appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Did you want a new file to be created each time you run the macro or do you want to copy the data to one file each time with the data added to the bottom of the existing data in the Input sheet?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Thanks for your quick reply...I'd like to create a new file each time and have the name of this file = to whatever is in cell A3 of the "Sched_Example" file.

  4. #4
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I've created a template file that replicates most of what I need:

    Dim strTemplate As String: strTemplate = "Path\template.xlsx"
    Dim wb As Workbook

    Set wb = Workbooks.Add(strTemplate)

    This works perfectly but I am not sure how to then cut and land the data into the newly-created workbook. It also obvi doesn't change the file name as I described earlier.

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Can you attach a copy of the template file? You can only change the name of the file when you save it. What is the full path to the folder where you want to save the new file? Is the template file in the same folder?

  6. #6
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Dim strTemplate As String: strTemplate = "C:\Users\havenc\Desktop\Upwork\Model_Template.xlsx"
    Dim wb As Workbook

    Set wb = Workbooks.Add(strTemplate)

    Yes, they would be saved in the same folder.
    Attached Files Attached Files

  7. #7
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I assume you want to copy the data from the "Outputs" sheet in Sched_Example to the "Input" sheet of the template starting in column P. If that is correct, the setup of the 2 sheets is different in that the "Input" sheet of the template has formulas in rows 6, 10 and 11. If we simply copy the data from the "Outputs" sheet in Sched_Example to the "Input" sheet of the template, the formulas will be overwritten. Could you please clarify in detail which rows of data you want to copy from the "Outputs" sheet and exactly where in the "Input" sheet you want to paste that data.

  8. #8
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    All apologies,

    => B12:M12 in "Sched_Example" would paste into Q4:AB4

    => B13:M13 in "Sched_Example" would paste into Q5:AB5

    => B14:M15 in "Sched_Example" would paste into Q8:AB9

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Place this macro in a standard module in the Sched_Example workbook and run it from there.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    It doesn't like the file saving portion at the end. I've tried removing that part but I still get errors. How can I remove that step and keep the rest functioning?

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I've tested the macro using the files you posted and it works properly for me. Is the macro at least opening the template file? Do you get an error message and if so, what is the message and which line of code is highlighted when you click "Debug"?

  12. #12
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Sorry for the confusion, full disclosure...I was trying to alter and use this code in a file that is too large to upload and/or email. I thought I could finagle some changes and do a "lift and shift".

    The file I'm pulling from is named, "VDER-HAC" and it is housed in the same folder as "Model_Template". The data is going to "Model_Template" just as before.
    I changed the range/rows that it is pulling (easy enough, see below). The filename value is found in a sheet in "VDER-HAC" called "User_Inputs", in cell C14.

    Sub CopyDataAndSave()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, desWS As Worksheet, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Outputs")
    Set desWB = Workbooks.Open(Environ("userprofile") & "\Desktop\Upwork\Model_Template.xlsx")
    Set desWS = Sheets("Input")
    With srcWS
    .Range("B8:M8").Copy desWS.Range("Q4")
    .Range("B45:M45 ").Copy desWS.Range("Q8")
    End With
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & srcWS.Range("A3").Value & ".xlsx", FileFormat:=51
    Application.ScreenUpdating = True
    End Sub

  13. #13
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Run this macro from the "VDER-HAC" workbook:
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Close...the new desired "Model Template" opens and cuts and pastes the data but with two issues:
    1. It's pasting formulas rather than values from the "VDER-HAC" file (I realize the example I sent you didn't contain formulas so that's my bad).
    2. The data that is being cut and pasted is found in tab "Detailed Outputs". The desired file name is on a tab called "User Inputs", so the source Work Sheets in the existing code are identical.

    I'm not familiar with having two Source WSs...many thanks for your help thus far.


    Please Login or Register  to view this content.
    Sub CopyDataAndSave()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, desWS As Worksheet, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Detailed Outputs")
    Set desWB = Workbooks.Open(Environ("userprofile") & "\Desktop\Upwork\Model_Template.xlsx")
    Set desWS = Sheets("Input")
    With srcWS
    .Range("B8:M8").Copy desWS.Range("Q4")
    .Range("B45:M45 ").Copy desWS.Range("Q8")
    End With
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & srcWS.Range("C14").Value & ".xlsx", FileFormat:=51
    Application.ScreenUpdating = True
    End Sub
    Please Login or Register  to view this content.

  15. #15
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Try:
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Awesome! It seems to work perfectly, many thanks again.

    My one long term concern is giving access to these files to others for their use. I'm assuming a new user would need to alter the des path in order for it work. I would have the template tabs in the "VDER" file so they could be copied via the macro, but it is a protected file. Any thoughts on this? I guess a new user would need to save both files in the same folder and then change the des file path.

  17. #17
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    You could have a file picker window pop up where the user selects the folder and the file to open. Would that work for you? What do you mean by "it is a protected file"?

  18. #18
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Well, there are only certain cells that you can manipulate/enter data into in the workbook, and you can't copy or move sheets.

    The interesting thing is you can change the macros in the file.

    I like your idea with the popup window and I think that would work.

  19. #19
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    ...just have never created a popup.

  20. #20
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Try:
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Perfecto...thanks!

  22. #22
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    My pleasure.

  23. #23
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    So, on this one...I use the code below (thanks again) to populate a file that I select called, "Model_Template" which I have attached. It works great.

    That said, how could I change this code to append each update into the same file. In other words, ideally each time I run this macro the three tabs found in "Model_Template" duplicate and populate with the new information, appending to the end of the tab series while keeping the existing models/tabs.

    Please Login or Register  to view this content.
    Sub Calculate()

    'Dim t1 As Double, t2 As Double
    't1 = Timer
    ' Application.ScreenUpdating = False
    Application.Calculate
    ' Application.ScreenUpdating = True
    '
    't2 = Timer
    'msg = MsgBox("Done; " & Round(t2 - t1, 0) & " seconds elapsed.", vbOKOnly, "Goal Seek Complete")

    Application.ScreenUpdating = False
    Dim desWB As Workbook, desWS As Worksheet, srcWS1 As Worksheet, srcWS2 As Worksheet
    Dim flder As FileDialog, FileName As String, FileChosen As Integer
    Set srcWS1 = ThisWorkbook.Sheets("Detailed Outputs")
    Set srcWS2 = ThisWorkbook.Sheets("User Inputs")
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select a file."
    flder.InitialFileName = Environ("userprofile") & "\Desktop\Upwork\"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set desWB = Workbooks.Open(FileName)
    Set desWS = Sheets("Input")
    With srcWS1
    .Range("B8:M8").Copy
    desWS.Range("Q4").PasteSpecial xlPasteValues
    .Range("B45:M45 ").Copy
    desWS.Range("Q5").PasteSpecial xlPasteValues
    .Range("B28:M28").Copy
    desWS.Range("Q8").PasteSpecial xlPasteValues
    .Range("B75:M75").Copy
    desWS.Range("Q9").PasteSpecial xlPasteValues


    Application.CutCopyMode = False
    End With
    ActiveWorkbook.SaveAs FileName:=ActiveWorkbook.Path & Application.PathSeparator & srcWS2.Range("C14").Value & ".xlsx", FileFormat:=51
    Application.ScreenUpdating = True

    msg = MsgBox("Calculation complete. See Outputs tabs and Project Model for results.")

    End Sub
    Please Login or Register  to view this content.
    Attached Files Attached Files

  24. #24
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I"m a little confused. From the file picker, you select the Template file, which you want to update. Which file is the source file containing the data you want to use to update the Template? Can you attach a copy of that file? Also, you mention 3 tabs in "Model_Template". Do you also want to update the "Dropdowns" sheet or only the other 2 sheets?
    Last edited by Mumps1; 05-08-2019 at 09:15 AM.

  25. #25
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    It's too large to attach but here is a link:

    https://drive.google.com/file/d/1D8S...ew?usp=sharing



    The second module (at the end) contains the section of code in question.

  26. #26
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    The "Calculate" macro as currently written, updates only the "Input" sheet in the Template with data from the "Detailed Outputs" sheet in VDER-HAC. Is this what you want?

  27. #27
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Yep, it feeds the cash flow tab. So each time I make a new one, it appends it to the end of the existing tab series...I've had to reassess and decided that all the models for each project should exist in the same workbook (for now) bc the linking was getting way too hairy.

  28. #28
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    What do you mean by "it feeds the cash flow tab"? I thought you wanted to update the "Input" sheet.

  29. #29
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I meant that the Input tab feeds the Cash Flow tab (it's irrelevant, was just pointing it out). I do want to update the "Input" sheet using the code.

  30. #30
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Try:
    Please Login or Register  to view this content.

  31. #31
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Thanks, that works great! Running the macro allows me select the "Model_Template" as its called for now where a new "Input" tab is added to the end of the file.

    The attached is the result of me running it twice to test, hence the new "Input" tabs. That said, you'll notice the "Monthly Cash Flow" tab links to the first "Input" tab.

    Is there a way that the "Monthly Cash Flow" tab could be duplicated as well to follow each new "Input" tab and then link to this new "Input" tab?

    The structure never changes, so mechanically it's a matter of changing "Input!" in all the "Monthly Cash Flow" formulas to the name of the new Input tab (2, 3, etc.

    If it's possible I can always change the name of each Input tab which would update the links. Basically, in a perfect world each "Input" tab would have its own "Monthly Cash Flow" tab.
    Attached Files Attached Files

  32. #32
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I tired several things and couldn't get it to work. The only way I can think of is to use the "Indirect" function in all the formulas in the sheet. You would have the sheet name in an empty cell such as A3 and have the formulas refer to that cell instead of using the actual sheet name. This would involve changing all your formulas to use this function. I tried to do that using "Find and Replace" but with no success.

  33. #33
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    No worries, I appreciate you taking the time to attempt.

  34. #34
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    My pleasure.

  35. #35
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Is it possible to at least create a new “Monthly Cash Flow” tab with the same values as the one in place (no updated links, just literally a copy of the last cash flow tab seen in the file)?

  36. #36
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Insert this line of code:
    Please Login or Register  to view this content.
    below this line:
    Please Login or Register  to view this content.

  37. #37
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Thanks...the Monthly Cash Flow tabs all contain Index/Matches to an Input tab.

    What does a formula look like where I have the sheet reference a cell value rather than a sheet name as you describe?

  38. #38
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    In the "Monthly Cash Flow" sheet cell A3 is empty. Enter the sheet name "Input" (no quotes) in A3. In cell C2 you have the formula: =Input!C5. Replace this formula with: =INDIRECT(A3&"!C5") This formula will refer to whatever sheet is entered in cell A3.

  39. #39
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Perfect!

    So I've altered the existing code to pull in more and/or different rows of data. It works well, but suddenly the problem I'm now having is just prior to the data populating I have to click through a series of named range warnings, even though I'm not pulling in any named ranges?

    "The name 'xxxx' already exists. Click Yes to use that version of the name, or click No to rename the version of 'xxxx' you're moving or copying"

    Here is the code:
    Please Login or Register  to view this content.
    Sub Calculate()
    'Dim t1 As Double, t2 As Double
    't1 = Timer
    ' Application.ScreenUpdating = False
    Application.Calculate
    ' Application.ScreenUpdating = True
    '
    't2 = Timer
    'msg = MsgBox("Done; " & Round(t2 - t1, 0) & " seconds elapsed.", vbOKOnly, "Goal Seek Complete")

    Application.ScreenUpdating = False
    Dim desWB As Workbook, desWS As Worksheet, srcWS1 As Worksheet
    Dim flder As FileDialog, FileName As String, FileChosen As Integer
    Set srcWS1 = ThisWorkbook.Sheets("Detailed Outputs")
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select a file."
    flder.InitialFileName = Environ("userprofile") & "\Desktop\"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set desWB = Workbooks.Open(FileName)
    Sheets("Input").Copy after:=Sheets(Sheets.Count)
    Set desWS = Sheets(Sheets.Count)
    With srcWS1
    .Range("B8:M8").Copy
    desWS.Range("Q4").PasteSpecial xlPasteValues
    .Range("B12:M12").Copy
    desWS.Range("Q11").PasteSpecial xlPasteValues
    .Range("B13:M13").Copy
    desWS.Range("Q12").PasteSpecial xlPasteValues
    .Range("B14:M14").Copy
    desWS.Range("Q13").PasteSpecial xlPasteValues
    .Range("B15:M15").Copy
    desWS.Range("Q14").PasteSpecial xlPasteValues
    .Range("B16:M16").Copy
    desWS.Range("Q15").PasteSpecial xlPasteValues
    .Range("B17:M17").Copy
    desWS.Range("Q16").PasteSpecial xlPasteValues
    .Range("B45:M45").Copy
    desWS.Range("Q5").PasteSpecial xlPasteValues
    .Range("B75:M75").Copy
    desWS.Range("Q9").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    End With
    desWS.Columns.AutoFit
    Sheets("Monthly Cash Flow").Copy after:=Sheets(Sheets.Count)
    Application.ScreenUpdating = True
    msg = MsgBox("Calculation complete. See Outputs tabs and Project Model for results.")
    End Sub
    Please Login or Register  to view this content.

  40. #40
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    When I tried the macro using the VDER-HAC file you uploaded, it worked properly without any warnings. If you have made changes to the VDER-HAC file, please upload the new version. Just a gentle reminder to use code tags when posting code. To do so, highlight the code and click the # icon in the menu.

  41. #41
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    No problem, the new VDER-HAC file is attached (via link) which feeds "SR_Portfolio" (also attached).




    https://drive.google.com/file/d/1XSO...ew?usp=sharing

    https://drive.google.com/file/d/1fea...ew?usp=sharing

  42. #42
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    There are a number of named ranges in the Portfolio workbook. You can use the Name Manager in the Formulas tab to see what they are. If you don't need the named ranges, then delete them and that should solve the problem. If there are any that you do need, I don't know of any way to avoid the warnings.

  43. #43
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    That seems to work well as you'll see in the attached (link). Many thanks.

    In the attached, what is the best way to sum each "Cash_Flow_xxxx" tab into the "Summary_Cash_Flow" tab while matching dates associated with each number?
    I've considered some sort of "index/match" based on a list of tabs via a macro?
    I've also considered modifying the "Aggregate" code you created a couple of weeks back that summed all tabs but not sure how to do so exactly.
    New tabs would be added intermittently via the macro you created. I'd like to capture those as well.

    https://drive.google.com/file/d/12DG...ew?usp=sharing

  44. #44
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    sum each "Cash_Flow_xxxx" tab into the "Summary_Cash_Flow" tab while matching dates associated with each number
    Please explain in detail using examples from your data.

  45. #45
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    The link to my example is here: https://drive.google.com/file/d/1b5n...ew?usp=sharing

    In this example you will see groupings of three tabs (grouped by color). In the "Summary_Cash_Flow" tab you will see that it is summing the cash flows found on each "Monthly_Cash_Flow" tab from each project.

    New sets of tabs will be appended to this file as new projects are added to the portfolio. I would like a macro that sums (aggregates) data as it become available (new projects added) just as it shown being added together on the Summary Cash Flow tab.

    The code you wrote to look through a folder and find similar data does this, but I am not sure how to alter this code to just work within a single workbook.

  46. #46
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I've had a look at your file and I can't seem to find a quick way to do what you want. The previous code I wrote used a loop and because the range was relatively small, it didn't take too long to complete. In your current file, you have 420 columns in multiple sheets and looping simply takes too long. Could I suggest that you start a new thread explaining this problem and perhaps another more experienced member will be able to help. If you find a solution, I would appreciate it if you could send me a private message with a link to the thread.

  47. #47
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I had another look. I realized that what was slowing the macro down was all the formulas calculating. The following macro turns off calculation until the macro runs and now it works fairly quickly. Give it a try.
    Please Login or Register  to view this content.

  48. #48
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Hey There,
    Sorry for the delay, I had to put the project in this thread to the side for a bit but now it's top priority again.

    The last macro you wrote to sum the cash flows works like a charm! Many thanks as always.

    That said it occurred to me that each project will likely not have the same start and/or end date. There was a code you had in the early stages of this that matched dates and values per date across many tabs and it worked perfectly.

    How can I add it to this "Sum" macro to properly aggregate values with the appropriate dates?

  49. #49
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I would have to review all the previous posts and macros that were used to try to remember what I did. It would make it easier and faster if you could attach a file with the varying start and end dates and explain in detail what the issue is.

  50. #50
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    In the attached, the one macro in the file (view macros obvi) sums all the lines across any tab with a "*_CF" name into the orange "Summary_Cash_Flow" tab.

    This works great, but while each of the *_CF tabs will have identical layouts and row names, the dates at the top will likely vary.

    That said I would like the macro to match the proper date with the proper amount, realizing I likely have to expand the date range at the top of the Summary tab to accommodate this function.

    To jog your memory (understood there's been so many iterations), the code below was used to crawl across files that were stored in a folder to perform a similar summation, the difference this time around is the tabs are all contained in one file and landing area is the same size as the source area.

    So really the bold portion was the magic behind this one:


    Please Login or Register  to view this content.
    Attached Files Attached Files

  51. #51
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Thank you for uploading. When I looked at the _CF sheets, I noticed that some of the dates in C2 of those sheets don't exist in the Summary_Cash_Flow sheet. Am I missing something?

  52. #52
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    No, you aren't missing anything. That's a little oversight on my part that I didn't consider in the mockup.

    If possible, Excel would (likely via this macro) grab the earliest date found in the "COD" column of every/any sheet and the formula to the right (=eomonth, 1) would calculate the remaining dates. Assuming that isn't possible, then change cell C2 of the ummary_Cash_Flow sheet to 1/1/18.

  53. #53
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I changed cell C2 of the Summary_Cash_Flow sheet to 1/1/18. This should work as long as the data is processed starting in column D because that column onward contains end of the month dates in all the sheets which can be found in Summary_Cash_Flow. The dates in column C in the _CF sheets are first of the month dates which are not included in Summary_Cash_Flow. Did I interpret correctly?

  54. #54
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    That's a good question, and yes, you are interpreting that correctly. That said, ideally the Summary tab in Cell C2 would retrieve the earliest "COD" date found in any of the tabs (ending in _CF) in cell C2.

    Given that the date found in cell C2 in subsequent tabs will nearly always be greater than said date, I would like the value associated with it to land in the month it is associated.
    As an (smaller) example I have provided an attached file. In said file, "Morristown" contains the earliest date found in cell C2 among "*_CF" tabs.
    This then finds its way into C2 of the Summary tab. C2 and all subsequent cash flows in the other *_CF tabs obviously occur later than 10/1/19.
    The dates that don't fall at the end of a month in these tabs would sum into the month with which they are associated in the summary tab, regardless of them occurring on the 1st.
    In the Summary tab of the attached example you'll see that Aldrich and Richland don't become part of the summation until November and December respectively based on their start dates.
    Attached Files Attached Files

  55. #55
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Isn't the earliest date in C2 in Silver_Creek_CF since it is 6/1/2019?

  56. #56
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    In the file posted yesterday at 10:24 AM, yes. I posted a smaller version in yesterday's 1:44 PM post for discussion's sake, but the concept applies regardless.

  57. #57
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    The dates are posing a problem. In the Summary_Cash_Flow sheet, the months in C2 and D2 are the same, both October. In all the other sheets, the months in C2 and D2 are consecutive months. This presents a problem with adding the values from whichever sheet has the earliest date in C2. In this case, that is sheet Morristown_CF where C2 has the month of October. Since both C2 and D2 in the Summary_Cash_Flow sheet have October, in which column, C or D would you add the values of column C from the Morristown_CF sheet? Does that make sense?

    You may want to note that the months in row 3 of the _CF sheets don't match the dates in row 2, although this wouldn't affect what you're trying to do.

  58. #58
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I see what you mean. I think in my rush (cutting and pasting) to make a mockup I created both issues that you point out.

    Cell D2 in the Summary tab uses "=EOMONTH(C2,0)" where D2 in the Cash Flow tabs was "=EOMONTH(C2,1)". I fixed each tab (and the month issue in row 3) in the attached file.

    So in this example (using the attached), "Morristown" is the earliest start date and ideally would begin the Summary tab on Row 2.

    To your point, Aldrich as an example have values in both C2 and D2 that fall into the month of November. In a case like this I would want both columns to land in the November 2019 column of the Summary tab even though the column specifically states 11/30/19.
    The ending month date is a catch-all for all values associated with said month/year in CF tabs.

    One exception might be if there were more than one tab where both had identical begin dates in C2 and D2, ex. two files with C2=10/1/19 and D2=10/31/19. This is unlikely but I suppose not impossible.
    In such an instance I'd want the values in each CF tab associated with 10/1/19 to aggregate under 10/1/2019 and those associated with 10/31/19 to fall under 10/31/19.

    In short, any values in CF tabs occurring after the earliest project "C2 date" would fall into the ending month with which they are associated on the Summary tab, regardless of being tagged on the first or last day of a month.
    Attached Files Attached Files

  59. #59
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Based on your explanation, I'm not sure that I can find a solution because of the increasing number of variables that have to be taken into account. If there could be a way of simplifying the process, then perhaps I could come up with something.

  60. #60
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    No worries, I appreciate you taking the time. Sometimes I'm asking Excel to perform miracles that aren't always realistic.


    That said, I've attached a simplified file that contains two important changes:

    1. There is a "Start" and "End" tab. Each new CF tab would be placed between these tabs (manually by the user, no macro involved). With this in place cell C2 on the Summary tab will always choose the earliest date available across the CF tabs.

    2. All dates are 1 month increments, all occurring at the end of a month. Values would sum across tabs based on EOM dates only.

    Does this help?
    Attached Files Attached Files

  61. #61
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    See if the attached file does what you want. In row 2 of the Summary_Cash_Flow sheet, I've had to add dates as far as the last date in any of the 3 CF sheets. This date was 12/31/54 in the Richland_CF sheet. You will have to make sure that you update the dates in row 2 of the Summary_Cash_Flow sheet as necessary. I've also deleted the "Start" and "End" sheets as the macro automatically calculates the date in C2 of the Summary_Cash_Flow sheet. You can simply add more CF sheets as needed as long as the Summary_Cash_Flow sheet remains the first sheet.
    Attached Files Attached Files

  62. #62
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I get an "object required" error. Performing debug highlights the portion in red/bold below:


    Please Login or Register  to view this content.

  63. #63
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    When I ran the macro on the file I attached, it worked without error. Did you get the message with the file I attached or did you try the macro on a different file? If on a different file, please post a copy.

  64. #64
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Yeah, I'm trying to lift and shift to another file. I changed the "deswb" based on the slightly different tab name, but still get an error near the bottom.

    File is in the following link: https://drive.google.com/open?id=160...MHLBqHzp6DYGhF

  65. #65
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    I found a couple of problems. First replace this line of code:
    Please Login or Register  to view this content.
    with this line:
    Please Login or Register  to view this content.
    The second problem has to do with the date in C2 in the Aldrich_CF sheet. It is not an end-of-month date and so it doesn't exist in row 2 of the Summary_CF sheet. I noticed that the drop down in C5 of the Aldrich sheet forces the user to select a date with a day of "28" which is not an end-of-month date except for February. Non of the other C5 cells in the appropriate sheets have a drop down list. When I manually changed the date in C2 in the Aldrich_CF sheet to "11/30/2019", the macro ran without errors.

  66. #66
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need VBA to create new Workbook and name it based on cell value in original file

    Sorry for the delay....seems to work, thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 10-17-2018, 02:35 AM
  2. [SOLVED] Need to modify macro to also create backup of original file
    By tfilipe in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-15-2018, 03:25 PM
  3. open worksheet in a different workbook based on cell name in original workbook
    By thara95 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-03-2017, 07:38 AM
  4. VBA that works perfectly in one file errors out in a new file based off the original!
    By carthesis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2016, 07:24 AM
  5. Replies: 1
    Last Post: 07-17-2014, 12:06 PM
  6. Splitting Excel file but want to create data links to original
    By hitchy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2013, 11:46 AM
  7. Trying to create a 2nd workbook to mirror the first, to protect the original
    By zachdking in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2013, 08:11 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1