Help with macro for Find, Copy, Paste, Repeat

[Tobbes]

I'm having trouble being a noob and all, understanding where to start with the following problem.

I have 10 worksheets. They are labelled 'FEB', 'MAR', 'APR', etc...to 'NOV' (NB: JAN & DEC not needed). In column "G" on all these sheets I have the data as "CURRENT" or "OVERDUE". The first 3 rows on every sheet are headers, so the data starts at 'G4' on every sheet. I then have an "OUTSTANDING" sheet which will display all of the "OVERDUE" items from the various months. I have an UPDATE_Click() event which when actioned needs to search all the column Gs in the month sheets and then copy the data from column 'B' to 'F' of the corressponding row to an "OVERDUE" result. The data from B-F then needs to be pasted in the next available row on the "OUTSTANDING" worksheet (starting at row 4, as first 3 rows are headers with merged and unmerged cells). Nothing fancy, when the search go does the column and hits a blank cell that means its the end of the list and can move on to the next month.

Any help is greatly appriciated!
Please include ample 'Comments in the code.

Cheers, Toby.

 

[jasonb75]

The '2' in the code is a column reference, the Cells (x,y) command uses numeric references, with 2 being column B. It can be done in a different way which would look an the last row used in all columns, but I couldn't find a sample of the correct code and everything I tried resulted in an error, so I used what I know works.

Also I think you're right about the merged cells, without testing my theory or confirming it with one of the forum's professional users, I would say that the way I did the last row check, with +1 to offset the row resulted in the code finding the last entry in row 2 because of the merged cells, the + 1 set it to row 3. Where as your Offset solution went from the same row, but recognised the merged cell and dropped the new entry into row 4.

Hopefully that makes sense.

Jason

 

[Tobbes]

I thought it might have been the column reference as it was kind of in an (x,y) co-ordinate format however, by stating range B4:G and then selecting down to the last row of data, what does the "Cells(Rows.Count, 2)" refer to? Are you making it count down the rows of column b?

To complicate matters worse, I'm now going to have a "MASTER" sheet where one of the columns (say "G" for instance) has due dates (again, starting in row 4). So now I need to search through the dates (in ddmmmyy format) to find the month and then copy B-F again and paste into corresponding month (i.e. 23Feb09 will be copied into "FEB" worksheet. So basically the same again but the array only needs to be the one sheet. I will have a tinker and see how I go with the already established code.

Thanks agiain champion!

 

[jasonb75]

That's right Toby, it is looking at column B, but instead of counting down, it searches up from the bottom, then finds the row that has the last entry. But with your merged cells it seems to miss the last one.

In this code you'll see a couple of alternative methods for things. I use internet banking to download my statements in CSV format to excel, and fortunatley I had a macro in there that sorts everything into individual Month sheets (among other things).

I've cut out the Month sort section and changed the ranges so it should fit to your "Master" sheet, also I've assumed that your headers will always be the same, so added a little getout clause to ignore emty cells in the first 3 rows when it's looking for the next empty row.

I haven't had chance to comment the code for you so let me know if you need anything explaining.

Jason

edit: ignore "dummy" in the array, added that in to swallow the 0 value in the array so that the months all fall in their correct positions, Jan = month 1 Feb = month 2, etc

Sub Macro3()
    Application.ScreenUpdating = False
indrow = 4
dmon = Array("dummy", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
 
While Worksheets("Master").Range("G" & indrow).Value > 0
destsht = dmon(Month(Worksheets("Master").Range("G" & indrow).Value))
Worksheets("Master").Range("B" & indrow & ":F" & indrow).Copy
Sheets(destsht).Select
lr = Cells(Rows.Count, "B").End(xlUp).Row
If lr < 3 Then lr = 3
Cells(lr + 1, "B").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
indrow = indrow + 1
Wend
Application.ScreenUpdating = True
End Sub

 

[jasonb75]

New code to replace module in my last reply, this does away with the need for the array by converting the date directly to the month for the sheet name. Slightly faster than the previous version but still takes a while (test sheet had 2000 entries, approx 30 seconds to run).

Sub Macro3()
    Application.ScreenUpdating = False
indrow = 4
While Worksheets("Master").Range("G" & indrow).Value > 0
destsht = Format(Worksheets("Master").Range("G" & indrow).Value, "mmm")
Worksheets("Master").Range("B" & indrow & ":F" & indrow).Copy
Sheets(destsht).Select
lr = Cells(Rows.Count, "B").End(xlUp).Row
If lr < 3 Then lr = 3
Cells(lr + 1, "B").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
indrow = indrow + 1
Wend
Application.ScreenUpdating = True
End Sub

 

[Tobbes]

Jason,

I was able to mingle with your code a bit and have got this to work:

Private Sub UPDATE_Click()
For cel = 4 To Sheets("MASTER").Cells(Rows.Count, 7).End(xlUp).Row
If Sheets("MASTER").Range("G" & cel).Value = "Feb" Then
'Wont find dates with Feb in them
Sheets("MASTER").Range("B" & cel & ":G" & cel).Copy
Range("B65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Next
End Sub

As you can see it searches for the string 'Feb'. However, it won't return the dates with Feb in them, only if the cell features 'Feb' by itself. I tried searching for '/02/' but that was unsuccessful too. The formula bar says the cell is 01/02/2009 and the formatting is ddmmmyy format. Do you know how to search within a date for a value like a month?

Thanks AGAIN! =]

Cheers, Toby.

 

[jasonb75]

Hi Toby,

Did you try the code that I posted without editing? It should have done the whole file without need for editing, as long as the sheet names for the months are all in the fomat of "Jan", "Feb", etc.

If it doesn't work, if you can let me know where it fails then I'll look into the problem for you.

The way you have done your code would require a macro for each month, in my code the line

destsht = Format(Worksheets("Master").Range("G" & indrow).Value, "mmm")

extracts the month from the date in the cell and turns it into text, so the same line works for all months. The "mmm" at the end of the code changes the format of the date in the cell for the macro to use, without actully changing the cell, so your sheet will appear the same.

Jason

edit:- I just realised from your code that it looks like you tried to edit the original copy paste code for this, the one you need it in reply #14

 

[Tobbes]

Jason,

Your code from post #14 works well.

In each of the month sheets I want to be able to run a macro that searches the master sheet and finds the relevant month and copies the data. Similar to looking for OVERDUE in the other macro however, I now need to search for FEB or MAR etc...
It will be 10 different macros where only the month criteria changes. Your post from #14 works, however errors when there is a different month in the master sheet.
Instead of searching through an array, I only need to search through the MASTER sheet.

Hope that explains it a little clearer?

Thanks all the same!

 

[jasonb75]

Hi Toby

The code in # 14 should be run from the master sheet, not from an individual month, that could be why it's producing an error.

As long as all of the dates in the master are formatted as dates it should run without problems. The only other thing that could cause it would be the sheet names, are they all named Jan, Feb, etc, first 3 letters of the month for the sheet? If not we would need to go back to the method used in #13.

I'll have a look at a rewrite of the code for you so it can run from the selected month if this doesn't do what you need.

Jason