Macro to find individual divisions then copy info

**markrennolds** · 11-13-2009, 03:09 PM

Hi All

I am comfortable with getting a macro to copy & paste where I need it to, however I am struggling with how to get the macro to recognise when a certain value has stopped.

For example in Column B on "DataDrop" are all the divisions that exist in my company i.e East Anglia, Essex, London etc. There is an empty row between each division.

I need the macro to look for the division name, sort into alphabetical order, copy & paste all the stores that come under that division (the number of stores alters each month) to ".2" B287 downwards and ".1" B3 downwards. There is other pasting that I need it to do but I can write the macro myself once I know how to get it to stop once there is a gap in the rows. i.e cells B2 to B32 for East Anglia, B34 to B75 for Essex.

The division names never change each month if that helps.

To summarise I need how to write a macro to recognise a blank row, stop, copy & paste then start from the next division down and so on. When pasting the info I also need it to know to start from the first available cell after the previous info pasted. If this is not possible I will have to just set up different areas on a worksheet to cover all possibilities.

Sample November Targeting Tool.xlsm

Thanks in advance
Mark

**mojo249** · 11-13-2009, 07:12 PM

Hi Mark

I don't understand exactly what you're trying to copy, from where and to where otherwise I'd have written some basic code for you.

To answer your question though, to recognise a blank cell you could try using:

Please Login or Register  to view this content.

If that doesn't help, maybe explain where a little more what you're trying to do and I'll see what I can do.

Dion

**markrennolds** · 11-14-2009, 04:22 AM

Hi Dion

Thanks for getting back to me. In easy terms I want a macro, lets call it 'copy EastAnglia, to look at sheet "DataDrop" search in column B for all rows containing "East Anglia" and copy all data in columns A to M. It then needs pasting via paste special > values into sheet ".1" cell A3.
Cells A3 to M450 would need their contents clearing via a clear contents macro which i can write. This would ensure all cells were emptied before the monthly data drop.

I then need other macros for London, Midlands etc. They need to do the same thing but paste into the next available row on sheet ".1" not paste over any data already there i.e A34 would be next available cell.

Hope this makes sense.
Thanks
Mark

Sample November Targeting Tool.xlsm

**mojo249** · 11-14-2009, 07:05 PM

OK I think I've understood correctly what you want to do. I've commented the macro (in module 3) to try to explain what the macro does.

I've listed each of the Areas in cells A1:A11 on a sheet called Data. The macro loops through each of the Areas, goes to the DataDrop sheet and loops through each row, copying the values to .1 sheet if the active row is for the current Area.

You'll notice I've looped through all rows for each of the Areas. Assuming the DataDrop sheet is nicely sorted by Area, then it would be quicker to find the first occurence for each Area then loop until you come to a blank row (as you suggested). But because there are only 400 rows on the DataDrop sheet, it only takes a moment to run.

The file wouldn't upload, so the code is below. I should point out that I'm using Excel 2003.

Dion

Please Login or Register  to view this content.

**markrennolds** · 11-15-2009, 08:30 AM

Thanks Dion

That all works great now.

Mark

Macro to find individual divisions then copy info

LinkBack

Thread Tools

Rate This Thread

Display

Macro to find individual divisions then copy info

Re: Macro to find individual divisions, then copy info where i need it

Re: Macro to find individual divisions, then copy info where i need it

Re: Macro to find individual divisions, then copy info where i need it

Re: Macro to find individual divisions, then copy info where i need it

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions