×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Audit a column then copy data to another sheet

Audit a column then copy data to another sheet

Audit a column then copy data to another sheet

(OP)
Well alrighty then...

I am trying to figure how how to automate a task on a spread-sheet with a macro function and don't quite know how to go about it. Currently we have to copy and paste.

In the lab that I work in, we use a spread sheet to report results of FAIs, the second to the last sheet of which is a listing of just out of tolerance features. I have automated the sheet to show which features are out of tolerance with conditional formatting and I use some true false statements to report "Accept" in one column if the feature is good and by default it keeps "Reject" in the next column if the feature is bad. The report has 4 columns in which to enter numerical values. If any of the four are good, it will show "Accept", if any are bad or blank it shows "reject."

Now that I've really made it confusing...

I want to build a macro that will automatically paste my rejected features to my "Out of Tolerance" page without skipping any rows. For instance, if I have ten rows with no rejects, I want it skip those rows, and also only copy the numeric value that is out of limit.

Im using Excel 2003 BTW.

If anybody has any ideas, please let me know.

RE: Audit a column then copy data to another sheet

It's not that difficult a task in VBA.  You need to set up one loop to test your conditions that currently report "Accept" or "Reject" and if they're true, then to perform some actions or else go to the next one.

The easiest way to go about this is to record a macro while you do these pastes to your "out of tolerance" page.

You'll need several variables to remember your place on each sheet, but that's no trouble at all.

The hardest part will be getting the loop correct, and the hardest part of that is specifying when to stop looping. Is your data column a fixed length? This will make it easier, because then you can use a "loop until activecell.address = '$B$5'" type statement.  If your data column varies in length, then you can loop until your cell is empty, but if you have empty cells in your column and you want to keep going then you'll need to find a creative way of specifying the end point of the loop. It's never insurmountable, sometimes just challenging.

My post at the end of this thread has a commented macro that includes a loop and an If Then Else statement that ought to point you in the right direction.

Assuming you keep your "Accept/Reject" field (which you could actually replace with the macro if you're feeling froggy) then you would set up a loop as follows:

(Paste into a new Module in the VBA window to reveal the handy color coding!)

CODE

Dim strTest as string 'set your variable

strTest = range("C3").Value 'where cell C3 is the first in your column of "Accept"/"Reject"

Do
   If strTest = "Accept" Then
      'insert statements for pasting to your other sheet
   ElseIf strTest = "Reject" Then
      'go to next -- i.e. do nothing
   Else
      'something's gone wrong, so abort!
      MsgBox "Oh poo" 'a message pops up to tell you you've had a problem
      Exit Do
   End If

ActiveCell.Offset(1,0).Select 'go to the next cell in the column
'note that Offset(1,0) means offset 1 Row and 0 Columns
strTest = ActiveCell.Value 'this is the easiest way to reset your test string

Loop Until strTest = "" 'loop until your encounter an empty cell in your column.

You'll need an addition variable to set the location on the other sheet, the easiest way to do that is to use the "Address" function. You can extend strings with a + as follows:

CODE

str1 = "Sheet1"
str2 = ActiveCell.Address
str3 = "'" + str1 + "'!" + str2

str3 would then contain the string "'Sheet1'!$B$4" assuming the active cell was B4.  You can similarly use ActiveSheet.Name to set str1 equal to "Sheet1".

Good luck!

RE: Audit a column then copy data to another sheet

(OP)
Hey, with a couple of mods to make it specific to my spreadsheet, I think this just may work! I'll have to do a little bit of modification to my spreadsheet, but I think this is a winner.

Thanks a lot!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close