Copying row based off cell value to a sheet of the same name

Ok, very new to VBA/macros and all that...

Here's my scenario...

I have a document that has a sheet for each day of the week, Oct 1, Oct 2, Oct 3 example...

If on sheet "Oct 1" column G:G has Oct 2 entered as the value, then that entire row will be copied to the 1st blank row of "Oct 2"... and so on for all sheets....

I'v seen vbas that will copy row based off a value, but not to a sheet of the same name of the value being looked at..

Any advise appreciated.

Rob

Rob;
Try this

Please Login or Register  to view this content.

Originally Posted by hambly

Now I think about it, it's probably just copying over onto the same row of the new sheet, so I just need to have it find the next blank row...

I can't see any reason why it doesn't copy every row onto a new row on the worksheet. It is finding the next blank row for every entry in G:G. If it's missing an entry from G:G, I would assume that there%

Ok;
Here's your file back.

It was finding the next blank cell in column "A", not column "J", so it kept overwriting the same row.

It was skipping a row on each sheet. If you want it to skip a row then change ".Offset(1)" back to ".Offset(2)".

It can skip rows that have already been copied in several ways.
1) When a row is copied put a "True" in column "Z" then have the macro look in column "Z" for "True".
2) Search the sheet copied to for a unique sequence. If Contractor # will only be used once per sheet, then search on sheets("Mar13") for "105", etc.
In short there has to be a cell or combination of cells that will NEVER be duplicated under any situation, then the macro can search for that and skip the row if it is found.

But # 1 is easier and could be faster if there are a lot of rows.

Here's the file that marks a row as copied. Change the "AD" to whatever column you want.

To change it from Copy to Cut requires more than a simple change. You have to start at the bottom of the column and work your way up. If you cut & paste row 3, row 4 moves up to become row 3, but the macro moves down a row to the new row 4. So the original row 4 gets skipped.
It could also just cut and paste row 3 over & over again (because row 4 moves up to become row 3 after row 3 is cut).

It's fairly easy, just different coding.

I don't like cutting a row at the top of the sheet. Every time a macro cuts a row, Excel has to move everything up a row. If you have 60,000 rows of data, that might take quite a while. I personally would either manually delete rows (so I could manually verify that it got copied right), or have the macro delete all the copied rows at once at the end of the macro, so Excel only has to move all the rows up once.

I also don't like deleting data at all. For some reason it always seems that 6 months from now I wish I still had a copy of all the deleted data. And there is always a small possibility that the macro "burps" and deletes the wrong row, especially if you keep adding more macros to do more things.

If you can do it manually, then VBA can do it (at least I can't think of any exceptions).

Originally Posted by hambly

Playing with the vba I noticed that it will overwrite any rows that already have data in (manually entered). Would I be able to get the vba to look for the 1st blank row (after the headings)... I guess my question should be would you be able to alter the vba for this?

I don't understand, it already does that on my computer. It looks for the first blank cell in Column("I:I") and uses that row to copy into.

Something else I was looking into was adding vlookup formulas on these sheets based off the contractor sheet. I have the vlookup working fine, but is it possible for the vba to keep the vlookup formula for rows it doesn't populate?

Change the formula to

Please Login or Register  to view this content.

They will be overwritten when the macro runs, because the macro looks for blank cells in column "I"

Could a VBA be written instead of a vlookup?

Now I'm really lost. Maybe you should tell me what you are doing. I thought we already wrote a macro. What do you want to rewrite it for?

One thing I did notice is that all the vlookup formulas bloat the file size - The final file will have a sheet for each day of the month for a 3 month period.

You're right you will have a very bloated file. If you explain what you're trying to do (from beginning to end) we can help. Assume that you're teaching a brand new employee what you're doing and pretend that you don't have a computer. How would you do it on paper.

I don't believe in copying data and also leaving it in the original place.
I would always use formulas to display data that is stored on the contractor sheet.
I still remember the first time a client swore up and down that a sheet would never change and 6 months later something did change. People change their names, they move, etc.
(There may be occasional exceptions, but I can't think of any right now).

I also don't believe in having 2 sheets with the same format that both display the data. I would leave all the data on 1 sheet and use filters to display the data the way I wanted it displayed. If I needed a sheet for display and a different for raw data, I would always use formulas to display the data and I can't think of any reason for having more than 1 sheet with the same format for display. I would just have 1 sheet and I would type in the date at the top (r use a pick list to pick a date) and the formulas on the sheet would then display everything I wanted.

I do like to use a different sheet or form to do the actual data entry. The data entry sheet or form would have a "save" button to store it on the raw data sheet.

But everyone has to do the things they're comfortable with. I generally code a little less efficiently than is necessary to get the job done. The difference in speed is not noticeable and it's easier for the next person who has to maintains the file. I always assume the next person doesn't know as much as I do.

If my ideas sound like something you want to do, just let us know. There are plenty of people willing to help.

************************************

What do you mean by

the VLOOKUP just gets too big

? Does the formula get too long, or just too many, or what?

Why do you need a separate sheet for each day?

On the raw data sheet you can filter by any column. If you use "AutoFilter" it creates a filter for ever column. Just pick the column you want in the top cell and select what filter you want. If you don't like it just turn it off. Easy as pie.

You should use formulas like you were doing on Mar 13 for every column that needs data from the contractors sheet. Lock the cells and protect the sheet so that they can't be edited. Unlock ONLY the cells that need to be manually edited.

Try those and see if makes things easier.

I'm doing this on the fly without testing it, so back up before running it.

'***************************************
In the future change the values between these lines to change ranges.
'***************************************

'--------------------------------------------------------
I changed the code between these lines so you can see what was changed.
(In case something goes wrong).
'--------------------------------------------------------

Please Login or Register  to view this content.

If you're asking a new question that is completely unrelated to the original question, you should start a new thread.
Most beginners don't realize what everything that is needed to answer completely, so as long as you're just refining the original question it's ok to continue with the original thread.

You probably shouldn't mark the thread "Solved" until you're completely satisfied. You were lucky that I noticed that you added something to a "Solved" thread. I usually quit looking at threads that are marked "Solved". I just hadn't gotten around to unsubscribing to your thread yet, so it popped to the top of my subscribed threads.

If you do need to refine a "Solved" thread, it's probably a good idea to send a Private Message to me to let me know that you have added something. Be sure and put a link to the thread in the PM so I can find the thread.

'***************************************
In the future change the values between these lines to change ranges.
'***************************************

'--------------------------------------------------------
I changed the code between these lines so you can see what was changed.
(In case something goes wrong).
'--------------------------------------------------------

Please Login or Register  to view this content.

Boy, I really messed up that sub(). I'm embarrassed to claim it as mine.

I tested this file, and it's working right (I think).
One thing:
You asked that it check column A on the next sheet to find the next empty row. Since there is no data in Column A "Shuttle" it kept finding row 3 and replaced the previous data. I changed it so that it checks Column J (that made the most sense to me, since there had to be a value in Column J in order to know what sheet to copy to.

You can change it to whatever you want by changing the values between the '****************************

I have some suggestions 2 make your workbook more automatic and less prone to typos.

1) Create a PickList and put validation in the "Next Booking Day" column. That way the user won't be able to accidentally type "Mar 30" instead of "Mar30". The picklist could be designed to automatically update itself and add new sheets to the workbook with the correct name.

2) Name the cells in each worksheet that identify which columns to use in the macro (just make sure each name is scoped to the sheet not the workbook, so each sheet can have it's own set of names the same as all the other sheets). This would allow you to add, delete, rearrange columns, etc. without having to change the macro. (This would require # 3 also).

3) Have the macro copy the sheet format to the next sheet and add the names for that sheet, etc. So if you rearrange the current sheet the macro would automatically rearrange the new sheet's columns. This could be a problem if you copy data to Mar30 from Mar27, then rearrange the data on Mar28 and copy data to Mar30 a 2nd time, but it can be figured out.

Here's all the notes I think are relevant.

I did improve it a bit. I forgot about turning Events back on if an error occurs. Don't want to leave your workbook not working. Also rearranged things just to make it a bit more efficient.

I have deliberately used techniques that are easier to understand for beginners. Some of these things could be more effiecient but harder to understand.

Yes you should start another thread.

Sorry, I forgot that I changed it to use .UsedRange. It didn't matter as long as you had never used cells below the data, so I never caught it.

Make this change:

Please Login or Register  to view this content.

Put this in with the rest of the "Dim"s

Please Login or Register  to view this content.

Replace This Line

Please Login or Register  to view this content.

with these lines

Please Login or Register  to view this content.

My bad;

Change This line:

Please Login or Register  to view this content.

I'm curious;
How did the data get into the new sheet, but it didn't get marked TRUE in the current sheet?

This would require trying to find the name in the new sheet and checking all the values in the current row to the row in the new sheet and ignore some changes (like the date and True, etc).
That actually should be a sub of it's own and my sub should call the new sub to find and check the new data. I believe that it would be a lot more complicated
You already have a sub that will probably get more complicated as time goes by. You need to understand this one first.
I don't want to create something than has too much possibility of screwing things up.

All right I didn't understand.

This should be easy for you to figure out.
When the sheet locates which sheet to copy to:
Just put in a check "If shFrom is shTo Then".
Give it a try to figure out where to put it and what you want to happen when it's true or false.

Just thought I would point out something.

If the column with the Dates/Sheets in it was not being copied you would not have this problem.
This demonstrates one way that you may want to rearrange the sheet, which is an argument for naming the cells/columns so the macro would still work if you move the column out of the copy range.

Another solution would be to use a different column outside the copy range (say "Z") and put an equation in the current Date/Sheet column "=$Z1". Then the date doesn't gets copied but you don't have to move the current column outside the copy range.

I'm honestly not trying to be a pain.
Over the years I have seen patterns of behavior. People get help and don't try to understand what was done. Then later on they want a simple change and they have no idea how to make the change. And people who can help don't want to spend all the time trying to figure out what I've done in order to help.

I'm trying to help people do stuff on their own and only help when they get stuck. Unfortunately I enjoy coding so much I always seem to forget that I'm trying to help, not do the work for them.

You have a situation that you will want to expand in the future. It is not the best design and the current solution will not easily lend itself to modification. It's too late for me to help you design the macro yourself. I can only try and encourage you to try and figure out how the macro works so that you might modify it in the future by yourself.

Or you can take the easy way and just delete all the Dates from the new sheet, or change the Date to something that won't get copied (Like Mar 30 instead Mar30) so the macro skips over the row when the date doesn't have a matching sheet.

My bad. For some reason, I got the impression that you were familiar with coding and just being lazy.

Change this code:

Please Login or Register  to view this content.

I do believe that you will want to add/change this in the future, so I advise you to start learning. I believe this macro will start causing you headaches eventually or the users will start complaining when they want the workbook improved.