PDA

View Full Version : Copy and paste based on two criteria



BMWPRO1
08-31-2007, 11:38 PM
Hello,

I'm new to VBA. Trying to teach myself. I have a work project im doing. The first sheet in my workbook has a ton of information regarding sales of my company. To make this easy I will only use 2 column. On a different work sheet I need VBA to copy and paste a row from the first sheet to the second sheet based criteria from two column, column 1(month sold) 2(sales man). Please fell free to ask questions if needed. :hi:
</IMG>

RonMcK3
09-01-2007, 12:31 AM
BMWPRO1,

Since I'm also new at this, I would start by turning on the macro recorder, opening (or selecting) your file, selecting the source worksheet, and on it the cells you want to copy (representing one instance of the type you want to process) by clicking and highlighting, select the other worksheet, click on the target cell, click paste (or edit>paste special>values). Then, do everything else that you want to do in your VBA program (font, size, text/background color, borders, bold/italic, etc), walking through all the steps that you can do manually. Include all the steps you go thru that prepare the data of the first sheet before you begin selectively copying it to the second sheet.

When you are done, turn Record off. Open the VBE (alt-F11 on PC), find your workbook in Project Explorerer, click on the Module to see your code. To this you will need to add the logic you want the program to apply in selecting items and in placing them. Your macro will teach you a lot about how to do coding the VBA way and give you a leg up on your work.

Before you start writing code, write an outline of how your program will find the cells that you want copied and how you want it to determine where it places that information on the second sheet (a specific row and column, or next available?).
?Will your user enter a value (Col A) that the program will then search for?
?Or, Will the user specify a combination of two values (col A and col B) that it must find?
?Will need to look for and copy all instances of the selection value(s) or will there be only one in a worksheet?
?Do you want your program to sort your original data set, first, ordering it in some fashion? (This you can do manually and capture with the macro recorder.)
?Think ahead, when there are more than two columns, will there be more complex business rules that drive how WS1 data is selected and copied to WS2?
?Are there formulas in your columns on WS1 and, if so, do you want to copy them to WS2 or just the values they represent? (Again, macro recorder will capture your Edit > Paste Special > Values (or Formulas, or Column Widths or whatever.)
?Consider adding the (or some of the) additional columns now; copying a range of 10 cells is no harder than copying 2 cells.

These should get you started.:bug:

mdmackillop
09-01-2007, 01:39 AM
Hi Both,
Welcome to VBAX.
BMWPRO1, if you wish to post a sample (it's always easier than explanations), use Manage Attachments in the Go Advanced section.
BTW, It's better to use meaningful titles for the question. It attacts those who know about the subject and makes it seachable for other members/guests.
Regards
MD

Bob Phillips
09-01-2007, 02:22 AM
You could do it with a simple loop to look down the first sheet and check rows meeting the criteria, and then copy those across



Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iTarget As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "Aug" And _
.Cells(i, "B").Value = "salesperson" Then
iTarget = iTarget + 1
.Rows(i).Copy Worksheets("Sheet2").Range("A" & iTarget)
End If
Next i

End With

End Sub


of course if column a is dates you will need to extract the month.

And do you just want one criteria or many?