Creating sheets with pasted colour-marked rows through a nifty macro command, please
 

Hey guys,

I have an Excel spreadsheet that seems quite simple to work with. The catch? I've got tens of these to work with on a daily basis.

The idea is generally that I have this spreadsheet for monitoring orders on a certain batch. After 14 days, we determine if the orders were confirmed or if they were canceled whereas the orders are sorted in their own sheets (confirmed and canceled).

Generally, this is the process I have in mind: I use colour coding, good row or bad row to distinguish between rows. Green in this case represents confirmed orders, whereas red represents canceled orders.

Now, through a macro of some sorts, two new sheets are created, where each set of rows are copied and pasted.

The sheet of confirmed orders is essentially the same as the previous sheet (With all the entries), except it has only the green rows pasted into it, while the sheet for canceled orders should only have the red rows.



Now, in case it's required, here is the plain Excel file that I work with. It's "original.jpg"


In the files, "whatiwantittodo" 1 through 4 is what I want it to look like. Currently this is all done by hand.

What I would like is, when I do the colour-marking, and then (When I am finished applying the colour-formatting on the rows) input a macro, the rows are copied into two newly auto-created sheets, where good entries (green) are pasted into one of the sheets, and the bad entries (red) are pasted into the other sheet.

So in pseudo-code sense, I essentially want this:

while row=green
mark selection
copy row
create sheet
goto sheet
paste selection

And vice versa if it's for red rows.

I would really want to find a way to optimize this process through a handy macro. It would be a real time-saver. Can anyone please help me? I am using Excel 2010 on Windows 7.

Thank you very much in advance!

Creating sheets with pasted colour-marked rows through a nifty macro command, please
 

Arsaces wrote:

I have an Excel spreadsheet that seems quite simple to work with. The
catch? I've got tens of these to work with on a daily basis.

The idea is generally that I have this spreadsheet for monitoring orders
on a certain batch. After 14 days, we determine if the orders were
confirmed or if they were canceled whereas the orders are sorted in
their own sheets (confirmed and canceled).

Generally, this is the process I have in mind: I use colour coding, good
row or bad row to distinguish between rows. Green in this case
represents confirmed orders, whereas red represents canceled orders.

Now, through a macro of some sorts, two new sheets are created, where
each set of rows are copied and pasted.

The sheet of confirmed orders is essentially the same as the previous
sheet (With all the entries), except it has only the green rows pasted
into it, while the sheet for canceled orders should only have the red
rows.

Now, in case it's required, here is the plain Excel file that I work
with. It's "original.jpg"

In the files, "whatiwantittodo" 1 through 4 is what I want it to look
like. Currently this is all done by hand.

What I would like is, when I do the colour-marking, and then (When I am
finished applying the colour-formatting on the rows) input a macro, the
rows are copied into two newly auto-created sheets, where good entries
(green) are pasted into one of the sheets, and the bad entries (red) are
pasted into the other sheet.

So in pseudo-code sense, I essentially want this:

while row=green
mark selection
copy row
create sheet
goto sheet
paste selection

And vice versa if it's for red rows.

I would really want to find a way to optimize this process through a
handy macro. It would be a real time-saver. Can anyone please help me? I
am using Excel 2010 on Windows 7.

Try this. (Probably not the best way to do it, but it works for me under
2007.)

Sub makeReports()
Dim current As Worksheet, confirmed As Worksheet, canceled As Worksheet
Set current = ActiveSheet

'Create the new sheets.
'Names include timestamp. This allows multiple reports if necessary.
when = Format(Now, "yyyymmdd-hhmmss")
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Confirmed (" & when & ")"
Set confirmed = Sheets(Sheets.Count)
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Canceled (" & when & ")"
Set canceled = Sheets(Sheets.Count)

'Copy the headings row.
current.Cells(1, 1).EntireRow.Copy
confirmed.Select
confirmed.Cells(1, 1).Select
ActiveSheet.Paste
canceled.Select
canceled.Cells(1, 1).Select
ActiveSheet.Paste

'The actual work.
For L0 = 2 To current.Cells.SpecialCells(xlCellTypeLastCell).Row
Select Case current.Cells(L0, 1).Style
Case "Good"
current.Cells(L0, 1).EntireRow.Copy
confirmed.Select
confirmed.Cells(confirmed.Cells.SpecialCells _
(xlCellTypeLastCell).Row + 1, 1).Select
ActiveSheet.Paste
Case "Bad"
current.Cells(L0, 1).EntireRow.Copy
canceled.Select
canceled.Cells(canceled.Cells.SpecialCells _
(xlCellTypeLastCell).Row + 1, 1).Select
ActiveSheet.Paste
End Select
Next

'Not strictly necessary, but the marching ants bother me.
Application.CutCopyMode = False
End Sub

This assumes that you *always* use the "Good" and "Bad" styles, instead of
coloring the rows manually (or whatever).

--
Family are the worst customers!

Thank you so much, it worked like a charm! I was really beginning to lose hope and I know absolutely diddly squat when it comes to VBA. You see, I get tens of these daily, and most of them are much longer than this particular sample, so it really helps to have something like this to simplify the entire work process.

Again, thank you so much for taking the time and the effort! I was really beginning to fear maybe there was no solution to this :D

Creating sheets with pasted colour-marked rows through a nifty macro command, please
 

Arsaces wrote:

Thank you so much, it worked like a charm! I was really beginning to
lose hope and I know absolutely diddly squat when it comes to VBA. You
see, I get tens of these daily, and most of them are much longer than
this particular sample, so it really helps to have something like this
to simplify the entire work process.

Is there another spreadsheet or something that keeps track of what's
confirmed, canceled, or whatever? If so, you might be able to automate this
further, so that you don't have to mark the invidiual rows as "Good" or
"Bad".

Again, thank you so much for taking the time and the effort! I was
really beginning to fear maybe there was no solution to this :D

It was pretty simple, really.

--
I would grieve for him, if it were not for the sense of joy I now feel.