+ Reply to Thread
Results 1 to 11 of 11

MACRO to split new data into multiple tabs but not overwrite existing data

  1. #1
    Registered User
    Join Date
    01-08-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    MACRO to split new data into multiple tabs but not overwrite existing data

    Hello all,

    I'm very new to macros in excel. I have a spreadsheet where I am tracking user issues of a software that our office uses. Each issue is assigned to a category. I currently have a macro in the worksheet that will sort the data into tabs based on category. So, "Major Program Change" "Minor Program Change" etc. all have their own tab and there is a tab labeled "All submissions" that has a running log of every issue ever submitted (this is where the sorting macro lives). Each issue is assigned a status (i.e. completed, new, in progress etc.) and the status is maintained in the "All Submissions" tab and pulled to the other tabs when I run the macro. Each tab has a macro that allows me to move the issues up and down to prioritize them. Here's my issue. The macro sorts the data into the tabs just fine and all the data carries over, I am then able to prioritize them and put them in order but if I add any new issues to the the "All submissions" tab, assign their status and then click the button to sort into tabs based on category, it overwrites all the data I have in the tabs already and I have to prioritize from scratch.

    So essentially, data that has already been sorted and prioritized I want to remain and I only want new issues that are put into the "All Submissions" tab to be sorted and I want them to be at the bottom and then moved up or down based on priority. I hope what I'm asking makes sense. I've included the current "sorting" macro that I have in the workbook below for your reference. I have also attached the workbook with personal details omitted to give a better visual of what I'm talking about.
    Please Login or Register  to view this content.
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. Also, you should indent your code to make it easier to see the logic structure. --6StringJazzer

    Thanks for the help!
    Attached Files Attached Files
    Last edited by 6StringJazzer; 01-08-2016 at 03:54 PM.

  2. #2
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    Please let me know if this works.

    Please Login or Register  to view this content.
    <---If my answer helped, please click *

  3. #3
    Registered User
    Join Date
    01-08-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    Thank you for your response! I get the message "Run-time error '9': Subscript out of range". I'm not sure what this means.

    Edit: I figured out it was giving me the error because I needed to change the name of the workbook from "Sheet1" to "All Submissions" in the macro. The code then ran, but I added a new submission at the bottom to see if it would sort into the appropriate tab and it didn't so I think something is still up with it.
    Last edited by amo899115; 01-08-2016 at 05:05 PM.

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    Using your original code, try changing this line
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    To
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-08-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    Thanks for the reply! I made the change and it successfully sorted and didn't overwrite the existing data, which is good! But it sorted the original data along with the new submissions and put it all under the existing data, so the original submission and heading are duplicated. I took a screen shot so you can see what I'm talking about. examplemacro.jpg

  6. #6
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    This should work.

    This time I remembered to set the worksheet name to what you have. I also stood on JLGWhiz's shoulders to incorporate his last solution into my code in order to handle new submissions (by changing the range as-needed rather than setting it to a constant value as I mistakenly had it before).

    All I did was use a Find method to get the row that matches the time stamp to store in StartRow_AllData so that when values are copied, they are not repeated.
    Please Login or Register  to view this content.
    Last edited by joe31623; 01-08-2016 at 06:24 PM.

  7. #7
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    *Sorry for the re-edit, but I incorporated a few If statements which should account for the possibility of populating a blank sheet. That would lead to the Find method returning "Nothing" and causing an error.
    **Updated 5:37 (EST) Line: If IsEmpty(ws_Parsed.Cells(StartRow_Parsed - 1, 1).Value) Then 'I think .Value is necessary here, but I'm not sure

    Please Login or Register  to view this content.
    Last edited by joe31623; 01-08-2016 at 06:35 PM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    amo899115

    Here's how I read your problem
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-08-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    Thank you for the reply! Sorry for the hiatus, I was away from work for the weekend. So I'm back at it this morning trying to figure this thing out. I used the edited code above but when I try to run it, it says "Run-time error '9': Subscript out of range" I tried glancing over the macro to make sure the title row was accurate and tab names and I can't figure out why it's giving me that error.

  10. #10
    Registered User
    Join Date
    01-08-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    Hi, thanks so much for the reply! I ran the code and it changed all the status' to "Completed" in the all submissions tab. My thought was they would stay however they were assigned, and just move to the different tabs based on category. Within each tab we prioritize the issues by using a macro to move entire rows up and down. With the first macro I had, when I sorted the "All Submissions" tab after adding new issues it would sort over the original prioritized data in the tabs. I was hoping to fix the macro so that it would keep the previously sorted and prioritized tabs as is and only sort newly added issues to the "All Submissions" tab that have yet been prioritized. I hope I'm making sense! Thanks again for taking the time to respond, it's appreciated!

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: MACRO to split new data into multiple tabs but not overwrite existing data

    See.................
    Attached Files Attached Files

+ 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. Deleted
    By MaxStrong in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2015, 01:50 AM
  2. Macro to recognize new data on main tab, and add to existing tabs by relation
    By Durbo94 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2015, 04:55 PM
  3. Macro to split data into tabs
    By keeptabs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2013, 05:56 AM
  4. Split data from Master Sheet to existing worksheets and overwrite data
    By vmwest in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-21-2013, 05:36 PM
  5. Split Data in Home Sheet to Multiple Existing Sheets
    By tboyle35 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2012, 04:23 PM
  6. [SOLVED] Split data in to multiple tabs using value in column
    By Manpar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2012, 08:33 AM
  7. Overwrite existing data
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-21-2012, 09:10 PM

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