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:

Do
' Copy macro
Loop Until Activecell.Value = ""

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

Sub CopyData()

Application.ScreenUpdating = False

Dim varArea As String
Dim varStore As String
Dim varSourceRow As Integer
Dim varTargetRow As Integer
Dim varLastRow As Integer

'Clear range on sheet .1
Sheets(".1").Range("A3:L5000").Value = ""

'Determine the total number of rows on DataDrop sheet
Sheets("DataDrop").Select
varLastRow = Cells(5000, 1).End(xlUp).Row

'Set top row for DataDrop and .1 sheets
varSourceRow = 2
varTargetRow = 3

Sheets("Data").Select
Range("A2").Select
Do   'Loop through each Area on Data sheet
    varArea = ActiveCell.Value
    
    Sheets("DataDrop").Select
    varSourceRow = 2  'reset varSourceRow for the next Area
    Do   'Loop through all rows on DataDrop sheet and copy if it's for the current Area
        Cells(varSourceRow, 1).Select
        If ActiveCell.Offset(0, 1).Value = varArea Then
            varStore = ActiveCell.Value
            Range(Cells(varSourceRow, 4), Cells(varSourceRow, 13)).Select
            Selection.Copy
            
            Sheets(".1").Select
            Range(Cells(varTargetRow, 3), Cells(varTargetRow, 12)).PasteSpecial (xlPasteValues)
            Cells(varTargetRow, 1).Value = varArea
            Cells(varTargetRow, 2).Value = varStore

            Sheets("DataDrop").Select
            
            varSourceRow = varSourceRow + 1
            varTargetRow = varTargetRow + 1
        Else
            varSourceRow = varSourceRow + 1
        End If
    Loop While ActiveCell.Row < varLastRow
Sheets("Data").Select
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""

Sheets(".1").Select
Range("A1").Select

End Sub