Copying partial rows based on contents of one cell.

Hi, and thanks for taking the time to help me already. Vlady suggested that I post any questions regarding Macros here as he felt that someone else might be able to better assist in that manner. He has (I might add) helped a great deal with my problem, I am just hoping to find an easier route as my excel skills are evidently quite limited.

So here is my problem. Attached is a workbook that I have mocked up to show what I need to do. You will see that I have a 'Sales' sheet from which I have copied data in rows from one sheet to multiple other sheets. Now, the hard part is explaining what I want to do but I'll give it a whirl.

I have five categories: All, Week 1, Week 2, Week 3, and Week 4 each colored differently for ease. In the correspondingly colored tabs I have Mon, Tue, Wed, Thurs, Fri, and Sat. I want to copy data based on what day shows up in the 'N' Column into the tab named for that day but it needs copied as follows (note that while this sample shows a very limited number of rows in actuality I will have some 5000):
In the 'B' column you will see ascending numbers from which I will reference my copies:

Into the red tabs I need numbers ('B' column)1-20
Into the yellow tabs I need 1-10 and 21-30
Into the green tabs I need 1-10 and 31-40
Into the blue tabs I need 1-10 and 41-50
I will also need these to copy into the corresponding days for each color.

I modified a Macro for this but the problem I had with it was that it copied the entire row while I only need the selections that you can see in the colored tabs. Also I need to be able to copy the text only while not disturbing the formatting of the secondary sheets such as fill or other effects.

I was hoping to do this without the use of a macro because...well...I'm a bit afraid of them but the formulas that I have been show to make this happen are so complex that every time I try to even copy them somehow I screw it up. Any help (whether by formula or VBA) would be much appreciated.

Regards
Tom

Hi Tom

This seems do-able with a bit of VBA - I would hate to try to keep on to of any formula!

Questions:
- are the colours just for demonstration purposes, or will the 5000 rows be colour coded? I ask this because if you are not using the colours, I cannot see any difference that will put (say) row 15 in week 1 and row 21 in week 2.
- do you have any objections to renaming week 1's days Mon1 through to Sat1 ?
- will this be a one off exercise, or will you keep adding to Sales and expect the VBA to ignore already posted rows?

I'm sure that I can come up with some more questions but the above will do for now

Regards
Alastair

Nice one natefarm.

(Now I see the merged cells in column A!)

Regards
Alastair

TO aydeegee:
Questions:
- are the colours just for demonstration purposes, or will the 5000 rows be colour coded? I ask this because if you are not using the colours, I cannot see any difference that will put (say) row 15 in week 1 and row 21 in week 2.
There will be colors showing the difference between 'weeks' although weeks is just a filler. The 'categories' will change from time to time. I am just trying to make a 'master copy' that I will be able to modify later, thus the difficulty in the formulas.
- do you have any objections to renaming week 1's days Mon1 through to Sat1 ?
I have no objections to renaming, but again those tabs will also change from time to time.
- will this be a one off exercise, or will you keep adding to Sales and expect the VBA to ignore already posted rows?
'Sales' will be added to continually throughout the duration of the 'weeks'. It would be helpful if the Macro would just update but I am not sure how those work.

Part of why I wanted to do this with formula was because this will need to update continually and because the colored tabs will need to remain as they are (meaning that I need the fill and borders and any other formatting to remain untouched). The 'A' Column was just for illustration purposes, I had no intention of leaving it there, that said, however, I can leave it if it needs to be there, it certainly will not effect the manner in which this thing works for me (may even make it a bi easier).


TO natefarm:
I will try your VBA later....for now I am late for work.

THANKS!!
Tom

natefarm

I tried your code and, well...this is the reason why I am afraid of Macros (mostly because they are very much smarter than me)...all I got it to do was delete all my information in the colored tabs (don't worry, I always save a copy, and this is just a trial anyhow) and tell me that it had an error.

Run-time error '9':

Subscript out of range

Cheers
Tom.

When you get a runtime error, click Debug, and it will put you in the still-active code with the current command highlighted. You can hover over the variables to see the values, or copy and paste into the Immediate pane to try to determine what went wrong. Don't be afraid of errors. They're a very regular part of a programmer's life. You just need to work through them. If you still can't work it out, post the workbook and I'll take another look.

natefarm

I did that and what is highlighted is:

With Worksheets (DaySheet)

when I hover over that it says:

Worksheets(DaySheet) = <Subscript out of range>

Thanks
Tom

It's trying to reference a worksheet that doesn't exist. Did you rename your first week sheets to mon1, mon2, etc. as suggested? Hover over DaySheet to see what sheet it's trying to work with. If you can't figure out where it's getting that sheet name from, put debug stops on the two "Call CopyData" lines, run the code, and analyze at each stop, how it's building the name. You had mentioned that the 'A' Column was just for illustration purposes. The code relies on having the correct values in the A column as they were in your example.

Ah ha! I see what I did here. When I went to check this out the first time I did change the tab names as suggested but then I must have gotten sidetracked and closed it without saving...the next time I opened it I did not change them. So, I changed them this round and it worked like a charm. Now, because I know nothing about VBA coding I have a few questions:

aydeegee asked if I wanted this to update as I input more information into the Sales tab. it would be helpful if it could do that but its not a big deal as I can always just rerun the macro (I think?).

there will be a need to change the tabs from time to time as I am setting this up to work (hopefully) for a long time. How will I go about doing that it the code relies on the names of the tabs to operate?

as mentioned above the 'A' column was just input for illustration purposes. what will happen if I take it out?

Cheers
Tom

Originally Posted by TomRet

aydeegee asked if I wanted this to update as I input more information into the Sales tab. it would be helpful if it could do that but its not a big deal as I can always just rerun the macro (I think?).

You could set it up to add to existing data, rather than start from scratch each time, by deleting (or commenting out) the first part that clears existing data, but make sure that whenever you run the code, your Sales data is new data only, or you'll get duplications.

there will be a need to change the tabs from time to time as I am setting this up to work (hopefully) for a long time. How will I go about doing that it the code relies on the names of the tabs to operate?

as mentioned above the 'A' column was just input for illustration purposes. what will happen if I take it out?

I assume that you mean going forward there will continue to be week5, week 6, etc., forever, and you would be adding 6 new tabs each week? If so, the workbook could get unwieldy pretty quickly. I may have some suggestions, but I need to know where your heading with this. Also, what's the significance of having the weeks numbered, starting with 1? What determines the week? If it's Date Purchased, then maybe we could work with that instead of a week number.

If you mean that data must only be entered into the 'Sales' tab then that's fine. there will be no need to add data anywhere else.
If you mean that I should only run the macro when there is new data added then that goes back to my original question anyhow. If I have to run the macro every time I add new data that's ok, I just didn't know if there was a way self update (for lack of better term) like it would were it all done by formula.

There will actually be no 'week' headings. This is pretty much set up as it will be (other than a few more tabs that have nothing to do with this (which brings up another question*) with the current tabs. The workbook will last the duration of the tabs then I will have to make a new one. Each time a new one is made I will have to rename the tabs something different..
There was no real significance in naming them week1, in fact that column will change as well but whatever is in that column will be reflected in the colored tabs that coordinate with column 'a' (unless I can take it out).
The 'date purchased' column is completely irrelevant to the VBA, it literally just shows the date purchased witch can range anywhere from January to December.

*above mentioned question: what happens when I add more tabs for other purposes?

as for a bit more explanation this is a ticket form for a community theater. Each 'category' is a different play with 'all' being a season ticket (thus the need for 'all' to be copied into each set of tabs. The tabs are for the day of each performance so it is something that will change as well.

Hopefully that clears it up a bit.

Thanks again.
tom

It does some, though I'm usually a little slow grasping the concepts, and I'm still a little fuzzy, but I would like suggest a radically different approach, which may or may not work with everything you need. Could you get rid of all the colored tabs, leaving Sales only, and then just use column filters to have it show you the rows you want, which is what was ending up on the colored tabs before? This would eliminate all redundant data, as well as the issue about clearing out existing data. You could create formula columns if needed to assist in determining which rows to include in the filters. Then you could sort and filter to your heart's content, and always with current data. If the filtering became cumbersome, you could make it more user-friendly with a userform you could have pop up, on which you could have combo boxes, checkboxes, option buttons, or whatever you need to make sort/filter selection easy, then click OK to have the VBA apply the filters.

Well, and this shows how little I know about VBA, these need to be printed as a reference at the door when people come in to get their seats. I do understand that I am possibly making this too hard but this is the reason. I am taking over this job from someone else who took it over from someone else.....anyhow, it was set up basically like this except all the information in the colored tabs was fetched via a vlookup formula. the problem that I have with that is that vlookup will only search the first column (the numbers) so while it does copy all the information it needs it has to be done one of two ways. Either you input the number into your number column on the colored tabs (one at a time) as you receive a ticket request, or you go in with ctr F and 'find' each occurrence of 'day 1' (whatever that happens to be) and cross reference so you can input the number into the number column and have it 'lookup' the rest of the data.

I don't know much at all about filtering either so I am unsure if it can be done and printed filtered...if that makes sense.

I am rapidly seeing that, while I thought I had a fair handle on excel I really don't...at all.

Cheers.

Filtering is quite simple. Just click the Filter icon on the Data tab and it puts a filter dropdown on the column heading of each column. Then select the dropdown of the column you want to filter and pick which item(s) you want to include, e.g., on the Day column, select Monday. It will hide all non-Monday rows. You can add selections on other columns as needed. To get the rows back, reverse the process, or pick other items, or just remove the filters. You can print the filtered rows as needed. Play around with it and see if it might be a better solution. If not, nothing lost, something learned. Don't worry about the VBA for now. It could be added later if needed.

You are right, filtering is quite easy. I've been playing around with that but the problem that I have found is that each of my groups of colored tabs needs to add up to a separate total sheet. Secondarily I could not figure out how to 'filter' columns that I do not need other than to hide them (which can get tedious as I need a separate sheet for each 'day')
The only thing I could come up with then was to copy (via formula) every cell into a new sheet for each day needed (which is what I have now) then filter them and hide all the columns I don't need. That will be more work that taking each ticket number as it comes in and transferring it to the colored tabs.

I think code could handle your needs fairly easily. Then just click a button and it's done. But as I said earlier, I'm still fuzzy on what you're trying to do, partly because you're describing something that is different from the original example you provided (No column A. No 'Weeks'. Tabs that are not week days, etc.), so I feel like I'm working blind, and doing a poor job of it. A sample that is closer to real data would sure help.

I apologize. The original workbook that I have has so much information on it that it is extremely hard to 'desensitize' so I ried to recreate the book and have done an extremely poor job of it. It is certainly not your fault and I appreciate your attempts to help. I will try to make something closer to what I am trying to do and send it over (another problem is that the entire wookbook may be to large to send whole).

Maybe just tweak the original example a little, just for clarification?

Ok here it is. Sorry that it took so long; this is a sie project that I cannot work on during work.

I have modified the red tabs only to show all the formulas and such that I am using (the last Macro worked well but scrambled my formulas). I did not create each sheet as there are so many but I figure that you can see what I am doing here and how I need to modify only certain sheets.

If you have and questions please let me know.

Regards
Tom

I gather that each color group is a different play, and the Sales data is to feed into them, but you had mentioned eliminating column A. How would the play be identified without column A?

Your are absolutely correct, the 'no fill' section is people who have purchased season tickets (therefore they do not need to be input into the colored cells) then each group of colored cells indicate a different play. The previous Box Office Chair just used a color code to differentiate each play, however the more I think about it the more I think it would be helpful to keep column a as a reference simply because I have used it in formula on other sheets.

Thanks for the clarifications. I attached a version that should be a good start. Here are the adjustments I made to the sheets to make things work:

1. Added an Events named range to the Show Dates sheet. The Event Code is key. It must match the codes in Sales column A (other than ALL), AND it must match the first word in your event tabs (the 2nd word must be one of the Day values from Sales column N). I also put actual performance dates in the first example, which you could extend as needed. I made the Events range larger than needed to handle expansion. Adjust as needed. The top rows 1:9 on the sheet can probably be delete.

2. Put formulas in the event sheets header area (p1 1Fri and p1 1Sat for examples -- you can copy & paste the rest) to bring in the Events info. Note that the event code is hidden in F1 by making the font white. I did something similar on p1 Report.

3. Fixed the formulas in G:H header area so clearing the data won't mess them up. Copy & paste these as well.

4. The part of the code that clears previous data looks for "Ticket" in cell A9 and will only clear the sheets where it's found.

For your column A values, it might work better to enter a value on every row, rather than just the first of each group. Then you could enter them in any order and sort as needed. I think the code will work either way.

There are other things that could be done as well to automate the process, especially when creating new events. You might want to use data validation on several of your Sales sheet values as well. Hopefully this will give you some ideas. Let me know if you have questions.

except change it to (SalesRow, 20)

You got it.

Awesome, thanks. I'll try that and let you know what happens!

Cheer
Tom

Everything seems to work great here man! I'll mark this solved in a bit but I thought I'd poke around just a bit more because I have a couple more questions. If I need to start another thread for this please let me know.

In the Show dates page you made there are three columns denoting the dates of the show (I put a heading over them stating that they are, in relative order, 1Fri, 1Sat, 1sun...ect) is there a formula or some such that I could use to transfer said date to another sheet if the ticket was sold for the first Friday shows only? Also I have a column for names then one for a greeting (in the Sales column). Is it possible to get it to populate into the greeting cell 'Dear whatever the name is'?

Cheers and Merry Christmas!
Tom

Hey, never mind. I figured out the 'Dear' thing;

=IF(F12>"","Dear "&E12&",","")

The other problemis still getting the better of me though!

Tom

There is one more thing. Where the VBA deletes everything in the 'Day Sheets' from row 9 down, is there a way to keep cell formatting rather than have it deleted as well? I filled every other row with a light grey to make for easier reading (keeping track of the rows once the page is printed) but it goes to all no fill when I run the code.

thanks
Tom

Worked like a charm Alpha. Kudos

Regards
Tom

Thanks!

Sorry I haven't been responding. I'm enjoying the holidays with the family. I will get back with you on your question in a few days.

No problem, completely understandably. Enjoy your holidays!

Cheers
Tom

Originally Posted by TomRet

In the Show dates page you made there are three columns denoting the dates of the show (I put a heading over them stating that they are, in relative order, 1Fri, 1Sat, 1sun...ect) is there a formula or some such that I could use to transfer said date to another sheet if the ticket was sold for the first Friday shows only?

Tom, I'm sorry but I'm again not comprehending very well. I'm not understanding when, where, or how this transfer is to take place.

It is I who should apologize, I have often been told that I am hard to understand. Let me try to explain further and I'll send over a new copy of my workbook.

In the Show dates page you placed respective dates for each show. I need to transfer (copy rather) them into the sheet called SD Ltr (near the end of the workbook) at cells b37 - b40. I am facing two problems, one, evidently I do not understand the vlookup formula at all and two, if you look in cell g2 of that same page you will see that it notes (from the sales page) that these tickets are for the 1fri so, how do I go about saying basically:

if g2 = 1fri then copy (from Show Dates) the respective dates for each show

Also, in relation to the vlookup formula I wonder why all the formulas on that page are off in relation to the sales page (for instance in cell a5 the formula states: =VLOOKUP(F2,Sales,17,0) however the data that it is actually copying is in row 18 rather than 17). Any thoughts?

Cheers
Tom

Two more questions actually (and thanks so much for all the help, you've been wonderful). Is there a way to change the 'events' on sales page row A?
Will it mess up the VBA to extend the rows (I'll need some 5000)?

Originally Posted by TomRet

In the Show dates page you placed respective dates for each show. I need to transfer (copy rather) them into the sheet called SD Ltr (near the end of the workbook) at cells b37 - b40. I am facing two problems, one, evidently I do not understand the vlookup formula at all and two, if you look in cell g2 of that same page you will see that it notes (from the sales page) that these tickets are for the 1fri so, how do I go about saying basically:

if g2 = 1fri then copy (from Show Dates) the respective dates for each show

The confusion is in trying to pull data from two different tables (Sales & Events) in a single vlookup. Here's what I did in the new attached workbook: 1) On the Show Dates sheet, I inserted row 4 and entered the current column number. 2) Selected D3:I4 and named the range ShowDay. 3) On SD Ltr, I added an Hlookup formula in H3 to provide the ShowDay column number. 4)Inserted a new column A for the show codes (you can hide the column or define a Print Area to exclude it). 4) Adjusted your column B Vlookup to use the code. 5) Adjusted the column C Vlookup to pull in the date, using the ShowDay column number in H3.

I think this provides what you were needing on SD Ltr.

Originally Posted by TomRet

Also, in relation to the vlookup formula I wonder why all the formulas on that page are off in relation to the sales page (for instance in cell a5 the formula states: =VLOOKUP(F2,Sales,17,0) however the data that it is actually copying is in row 18 rather than 17).

Because your ticket numbers start in row two, so they're off by one from the row number.

Originally Posted by TomRet

Two more questions actually (and thanks so much for all the help, you've been wonderful). Is there a way to change the 'events' on sales page row A?
Will it mess up the VBA to extend the rows (I'll need some 5000)?

You can change change the event codes or add events as needed as long as they match the first word in the event sheet names, AND the event codes on the Show Dates sheet. No problem adding rows. It runs till it hits a blank in column 2.

Ticket Sales.xlsm

So let me dumb this down for my own clarification.

Here's what I did in the new attached workbook:

Basically what you are saying is that because I was trying to pull data based on two different locations you simply added a cell to pull date from the first source so that the formula could use data on a single sheet to pull from another?
Whatever you did it works perfectly.

Because your ticket numbers start in row two, so they're off by one from the row number.

Cool, I thought that might be the reason but I have found out how little I know here.

You can change the event codes or add events as needed as long as they match the first word in the event sheet names, AND the event codes on the Show Dates sheet.

So where both the event and sheet tabs say P1 I would just change them to...say DS and it would work?

I did not even know that you could name a range like you did with "ShowDay" that is an awesome tip. Thanks!

Cheers
Tom

There are other named ranges referred to in earlier posts and formulas, including Events (used in daily event sheet formulas) and Sales (used in SD Ltr).

I noticed that. I was trying to evaluate the formulas (because I want to learn all of this so I'm not bothering people all the time) and I could not figure out where 'Events' was located.

Another question is; if I needed to take out a column (I am told now that we no longer need the authentication codes {column D}) how would I do that without messing up the code? I tried and everything went south....

Thanks
Tom

The quickest way to find a name is to select it from the cell name box (upper left, below the ribbon). To adjust named ranges, select Name Manager in the Formulas tab on the ribbon. You should probably do a lot of cleanup there. Any time you see a #REF! in a name, it's an invalid reference and should be deleted or fixed. It appears there were invalid references on an original sheet that were duplicated when the sheets were copied and pasted.

To fix the code after deleting column D (column 4), look through the code for "Cells(SalesData, n)". Whenever n is > 4, subtract 1 from it.

Hey man, I can't thank you enough. You are awesome dude! I'm calling this solved!

Cheers
Tom