Creating a Macro to copy rows with certain criteria from multiple sheets in to a new sheet

B

bailsmb

New Member
Joined
Oct 13, 2015
Messages
8
I'm sorry about the complicated title.

I've reviewed the site and I've seen a couple threads with Macros, but I've never worked with macros so it's over my head.

What I'm trying to do is copy rows from any of the sheets where the status is neither "Resolved" or "NA" onto our main Exception tracking sheet.

For example:
Sheet = Third Party
A B C D
1 Bob TEXT TEXT
2 Dave TEXT Resolved
3 JANE TEXT NA TEXT

Sheet= Operations
A B C D
1 SUE TEXT Resolved TEXT
2 GREG TEXT
3 TINA TEXT TEXT


My sheet entitled Exceptions would show:
A B C D
1 Bob TEXT TEXT
2 GREG TEXT
3 TINA TEXT TEXT


How could I create a macro to accomplish above, and leave the data in it's original location.

Lastly, on the "Exception" sheet, would I be able to put an "Update" button to re-run above as it is being worked?

Thank you for your assistance.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
I'm sorry about the complicated title. ****EDIT TIMED OUT****

I've reviewed the site and I've seen a couple threads with Macros, but I've never worked with macros so it's over my head.

What I'm trying to do is copy rows from any of the sheets where the status is neither "Resolved" or "NA" onto our main Exception tracking sheet.

For example:
Sheet = Third Party[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]BOB[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]DAVE[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"]RESOLVED[/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]JANE[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"]NA[/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
</tbody>[/TABLE]



Sheet= Operations
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]SUE[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"]RESOLVED[/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]GREG[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]TINA[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



My sheet entitled Exceptions would show:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]BOB[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]GREG[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]TINA[/TD]
[TD="align: center"]TEXT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TEXT[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



How could I create a macro to accomplish above, and leave the data in it's original location.

Lastly, on the "Exception" sheet, would I be able to put an "Update" button to re-run above as it is being worked?

Thank you for your assistance.
 
Upvote 0
bailsmb,

Thanks for the Private Message.

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


3. Do your three worksheets NOT contain titles in at least row 1?

4. Is there other text in worksheets Third Party, and, Operations, in column C?
 
Upvote 0
bailsmb,

Here is a macro solution for you to consider, based on your flat text displayes of the three worksheets.

Sample raw data worksheets:


Excel 2007
ABCDE
1BOBTEXTTEXT
2DAVETEXTRESOLVEDTEXT
3JANETEXTNATEXT
4
Third Party



Excel 2007
ABCD
1SUETEXTRESOLVEDTEXT
2GREGTEXTTEXT
3TINATEXTTEXT
4
Operations


And, the results after the macro:


Excel 2007
ABCD
1BOBTEXTTEXT
2GREGTEXTTEXT
3TINATEXTTEXT
4
Exceptions


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Compare Text
Sub bailsmb()
' hiker95, 10/13/2015, ME894007
Dim we As Worksheet
Dim c As Range, lr As Long, nr As Long
Application.ScreenUpdating = False
Set we = Sheets("Exceptions")
With Sheets("Third Party")
  lr = .Cells(.Rows.Count, 1).End(xlUp).Row
  For Each c In .Range("C1:C" & lr)
    If c <> "RESOLVED" And c <> "NA" Then
      nr = we.Cells(we.Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And we.Range("A1") = "" Then nr = 1
      we.Range("A" & nr).Resize(, 3).Value = .Range("A" & c.Row).Resize(, 3).Value
      we.Range("d" & nr).Value = .Range("E" & c.Row).Value
    End If
  Next c
End With
With Sheets("Operations")
  lr = .Cells(.Rows.Count, 1).End(xlUp).Row
  For Each c In .Range("C1:C" & lr)
    If c <> "RESOLVED" And c <> "NA" Then
      nr = we.Cells(we.Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And we.Range("A1") = "" Then nr = 1
      we.Range("A" & nr).Resize(, 4).Value = .Range("A" & c.Row).Resize(, 4).Value
    End If
  Next c
End With
With we
  .Columns("A:D").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the bailsmb macro.
 
Upvote 0
Thank you Hiker 95.

I am running Excel 2013 on a Windows 7 Machine.

All the sheets have a title in row 1. One sheet, however, has an intro in row 1 and titles in row 2.

As far as the column, if it is anything other than Resolved or NA, than I want it to move to the first page.

I did try to update my signature to reflect what is similar to yours. I remember seeing it when setting up, but bypassed it. Now, I'm not able to locate it. Should I go to another forum to find out how to set up signature? :laugh:
 
Upvote 0
Thank you Hiker 95.

I am running Excel 2013 on a Windows 7 Machine.
Click to expand...

bailsmb,

Thanks for the feedback.

You are very welcome. Glad I could help.

All the sheets have a title in row 1. One sheet, however, has an intro in row 1 and titles in row 2.
Click to expand...

In order to continue, and, so that I can get it right on the next try:

Can you post screenshots of the two actual raw data worksheets, Third Party, and, Operations?

And, can you post a screenshot of the worksheet Exceptions (manually formatted by you) for the results that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try the following:

Click on the below link to see How to display your sheet, and, how to install, download, and, use the MrExcel HTML Maker:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

To test the above:
Test Here


The following is a free site:

Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

If the above site does not work for you, then try:
https://dropbox.com
 
Last edited:
Upvote 0
Hiker95,

Thank you so much for your help. I have no clue, and still no clue how to create macros. :huh:
You are amazing.

I ran the macro above, and I was able to get some of the data. I do have a couple questions.

1.) Is it possible to have the "exceptions" worksheet show the sheet the data came from?
2.) How can the macro be worked to wrap text in cells as well as be wide enough. For example, Column B isn't quite wide enough, and Column D is huge.
3.) How can I have the entire row copy over? The spreadsheet goes to Column L but is only copying data up to Column D.
4.) Since this will be a live spreadsheet, is it possible that when the macro is run, it clears out the prior data on the "Exceptions" tab and replace with fresh data?

I feel like an ingrate asking for more assistance. I'm sorry.:banghead:
 
Upvote 0
bailsmb,

In order to continue, and, so that I can get it right on the next try, I will have to see your actual workbook/worksheets.

Please follow my instructions in my reply #6.


If you are not able to provide screenshots, or, your actual workbook/worksheets, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
bailsmb,

Thanks for the latest workbook.

I am not able to understand what I am seeing in your latest workbook/worksheets.

They do not resemble in any fashion what you posted in your original reply #1, and, reply #2.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Macro to pull Office 365 'Comments' onto new tab like I previously did with what's now 'Notes'
Replies
1
Views
118
jkouvel
J
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
166
Lux Aeterna
L
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
165
NdNoviceHlp
NdNoviceHlp
M
  • Question Question
Adding a link in email macro using a cell value
Replies
3
Views
451
KOKOSEK
KOKOSEK
S
  • Solved
copy data from userform to multiple sheets based on checkbox selection
Replies
4
Views
133
Starchaser
S

Forum statistics

Threads
1,223,789
Messages
6,174,576
Members
452,573
Latest member
Cpiet

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top