+ Reply to Thread
Results 1 to 5 of 5

Split Macro - add formulas to copied sheet

  1. #1
    Registered User
    Join Date
    01-13-2021
    Location
    Wisconsin, USA
    MS-Off Ver
    10
    Posts
    4

    Split Macro - add formulas to copied sheet

    I have created a macro that splits into files based on a column on the spreadsheet. In this same macro I'm also copying an additional spreadsheet (another tab) from the same workbook into the new workbook that is created. Is there a way that I can populate some of the fields on this new worksheet based on values in the new spreadsheet - the contact name will always be the same on each file and is in column B. then I would like to sum up all the values in column S and add that to this new sheet that is added.

    Here is my code.
    Please Login or Register  to view this content.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,310

    Re: Split Macro - add formulas to copied sheet

    Hi and welcome to the forum.

    Just a thought but I suspect that code could perhaps be simplified considerably

    Whenever you find yourself creating looping code (like your Do...Loop) stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop. That said you don't change the value of the Done variable which remains at zero so it's not clear why you have a loop at all.

    Why not upload the workbook and explain exactly what should happen after the macro has processed. Show us what you start with and manually add the result you expect to see.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-13-2021
    Location
    Wisconsin, USA
    MS-Off Ver
    10
    Posts
    4

    Re: Split Macro - add formulas to copied sheet

    SampleInvoiceInfo.xlsx - is the main excel workbook that I'm running the macro against. There will be a new Invoice excel workbook each month
    SampleInvoiceInfo_(column A value).xlsx

    When the macro runs it will look at the data that I have specified in the user form:

    Which Column = A
    How Many Header Rows? = 8
    Name of Addl Tab to copy (leave blank to ignore) = InvoiceConfirmation
    Where to save the file = full path

    The macro splits into separate files based on the column I specify and which header row this value is in - so in this instance I specified Column A which is the campus and header rows = 8.
    The new Excel workbook name will include the value in Column A in the file name.
    It will also include all header rows in addition to the specific transaction rows for that campus in the new file.
    It will save to the location I specify.

    What I would like added is after it splits by column A (campus) I would like the values from the new sheet created (Sheet1) populated on the InvoiceConfirmation tab.
    I would also like the values summed in the total amount column and placed where it needs to go
    And the requestor name from the file added to this tab as well in the appropriate place.
    I tried to add a formula on the actual main spreadsheet to look at Sheet 1 but that wasn't working so I removed. Maybe I need to do the formula a certain way.

    Any help you can give would be greatly appreciated. Also if you think the macro be made more efficient as well that would be great! When the macro is run there could be thousands of rows of data that it needs to sift to and there could be up to 13 campuses that it will be splitting by.

    Thanks!
    Maria
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-13-2021
    Location
    Wisconsin, USA
    MS-Off Ver
    10
    Posts
    4

    Re: Split Macro - add formulas to copied sheet

    I forgot to add that I actually added a few new variables to the user form. Here is the new code.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-13-2021
    Location
    Wisconsin, USA
    MS-Off Ver
    10
    Posts
    4

    Re: Split Macro - add formulas to copied sheet

    Quote Originally Posted by Richard Buttrey View Post
    Hi and welcome to the forum.

    Just a thought but I suspect that code could perhaps be simplified considerably

    Whenever you find yourself creating looping code (like your Do...Loop) stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop. That said you don't change the value of the Done variable which remains at zero so it's not clear why you have a loop at all.

    Why not upload the workbook and explain exactly what should happen after the macro has processed. Show us what you start with and manually add the result you expect to see.

    Hi Richard,
    I uploaded a workbook and the sheets of what I expect after the split files. Do you have any suggestions?
    Thanks!
    Maria

+ 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. Macro that copies sheet and places after the copied sheet every time
    By goatie43 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-08-2015, 10:27 AM
  2. Make macro on copied sheet refer to the new sheet?
    By ADJetz in forum Excel General
    Replies: 2
    Last Post: 11-27-2014, 10:57 AM
  3. Referencing a Different Sheet for all the formulas in a copied graph
    By michiganhockey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2014, 01:12 PM
  4. Macro Code to keep Formulas in Split Workbooks
    By Phil_Lloyd_1979 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-03-2014, 10:27 AM
  5. Change sheet macro name in a new copied workbook.
    By Richard Buttrey in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-27-2009, 03:22 PM
  6. Macro on copied sheet
    By beatnik in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-17-2008, 06:00 AM
  7. Delete the formulas of webpage, copied & pasted on excel sheet
    By Mustafa Abedin in forum Excel General
    Replies: 1
    Last Post: 06-19-2005, 10:05 AM

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