need help with writing a macro

I'm trying to write a macro that will copy the data (columns A-E) from a "shop" and paste it into a new sheet, i.e. all shops listed FT into the FT-Data Sheet, etc.

I thought the way to do this would be via a do loop, but I'm kind of stuck and don't know what to do. The current code seems to run indefinitely and not really do anything. I think the problem is it never exits the loop. The code is just for the "FT" shop. I think once one is built I can just copy/paste the code and change the shops so it will run for all "shops" (possibly an incorrect assumption).

So, as I said I'm stuck. Anyone have some suggestions on what to do/change? Thanks for your help.

The "VBA Testing - after" is what I want the file to look like after the macro runs. Also I should specify that I need to do this often and the number of #'s in each shop varies greatly from time to time.

Thanks again

Originally Posted by tyler3

I'm trying to write a macro that will copy the data (columns A-E) from a "shop" and paste it into a new sheet, i.e. all shops listed FT into the FT-Data Sheet, etc.

I thought the way to do this would be via a do loop, but I'm kind of stuck and don't know what to do. The current code seems to run indefinitely and not really do anything. I think the problem is it never exits the loop. The code is just for the "FT" shop. I think once one is built I can just copy/paste the code and change the shops so it will run for all "shops" (possibly an incorrect assumption).

So, as I said I'm stuck. Anyone have some suggestions on what to do/change? Thanks for your help.

The "VBA Testing - after" is what I want the file to look like after the macro runs. Also I should specify that I need to do this often and the number of #'s in each shop varies greatly from time to time.

Thanks again

Hi,

I guess it's always difficult to understand other peoples code.

For what you appear to be trying to do, ie, copy all of a shop type to the related sheet, a filter would work well.
Record a macro and set the autofilter, select the FT, copy and paste etc, to give code something like

Please Login or Register  to view this content.

which could then be modified for each sheet/shop.

For your code, at a glance the i would need to be Long (not integer), the 'Cell' remains empty and is never anything else (so you will never 'do, and will loop because 'Check' cannot get set to False).

I see no reason to have two loops, moreso when you read the above, one loop is defeating the other.

hth
---

Originally Posted by tyler3

Thanks, I'll try out the autofilter method.

Sorry for my horrible code, lol. I'm a n00b at vba.

the filter should work for you, as to the code, we all write better code than we did last year.

---

Thanks for your help. I tried it out and it works well.

One thing I was trying to accomplish with my previous code is to make the macro robust enough to work when the number of jobs in each shop varies. That's what the Do loop was attempting, poorly at that. Would the combination of maybe a search and if statement be a better approach?

I've been reading a bunch online and browsing through the vba help menus but I'm not sure what would be the best approach: if, do, do while, etc, maybe something really cool I don't even know about.

________________________________________________
edit, nvm I think using Cells.Select will work after filtering

Originally Posted by tyler3

Thanks for your help. I tried it out and it works well.

One thing I was trying to accomplish with my previous code is to make the macro robust enough to work when the number of jobs in each shop varies. That's what the Do loop was attempting, poorly at that. Would the combination of maybe a search and if statement be a better approach?

I've been reading a bunch online and browsing through the vba help menus but I'm not sure what would be the best approach: if, do, do while, etc, maybe something really cool I don't even know about.

________________________________________________
edit, nvm I think using Cells.Select will work after filtering

Hi,

Do loops have their uses, but they are resource hungry and slow down the response, the amount of delay being dependant upon the times around the loop.

If you would manually do it by selecting a range (ie copy a block etc) then you don't need a Do loop, use the block approach.

There are still times that you will need (say) 3 loops within each other, to process each row in a sheet, within the row to process each cell, within the cell to process each character, but avoid this slow process whereever possible.

As to when to use Do (Loop, when you just want to go around), For (Next, for row = 10 to 25) or If (GoTo, as a last resort) etc, the option that appears most useful at the time is generally the one I would select, some things just seem to fit. (It's not really scientific, but code works better if I'm happy to write it that way).

And when all else fails, search for and copy someone else's code, there is so much available.

hth
---