# If condition met copy and go to next Cell

This is a discussion on If condition met copy and go to next Cell within the Excel Questions forums, part of the Question Forums category; Hi There, i have 2 sheets in one workbook, Sheet1 will have a coulmn "A" Named "Offices" user can choose ...

1. ## If condition met copy and go to next Cell

Hi There,
i have 2 sheets in one workbook, Sheet1 will have a coulmn "A" Named "Offices" user can choose (Office No.1, Office No.2 .. etc) in this coulmn..

what i want is : in sheet2 to have a column that will search in Cloumn "A" in Sheet1 for "office No.1" if found
i want the entire row in Sheet1 to be copied to Sheet2 in Row "3"
and will continue search if other "Office No.1" appeared copy the entire row in the next row "4"

know that when searching in sheet1 i want it to search from A3 to A100.

i hope i explained very well.. waiting for your help ..

2. ## Re: If condition met copy and go to next Cell

Hi, you can do it two ways
1) With a filter.
You can create a filter for "office No.1" for column A in sheet1 and copy + paste into the sheet2.

2) Run a macro (The following code has not been tested as I recommend the first method)
Code:
```Sub condCopy()
Dim LR As Long, i As Long, count As Long
LR = Sheet1.Range("A" & Sheet1.Rows.count).End(xlUp).Row
count = 3

For i = 2 To LR
If Sheet1.Range("A" & i).Value = "office No.1" Then
Sheet1.Range("A" & i).EntireRow.Copy Sheet2.Range("A" & count)
count = count + 1
End If
Next i
End Sub```

3. ## Re: If condition met copy and go to next Cell

Thanks for your reply..

for the 1st method this manually, but what i want is when i fill the info in Sheet1 i want it to distribute it Automatically in Sheet2 for "Office No.1" and Sheet3 for "Office No.2" and so on.. of course by the formula or code put in each sheet ( Sheet2, Sheet3, ...etc )..

for the 2nd method i tried it in Sheet2 but didn't work..

Thanks for your help though ..

4. ## Re: If condition met copy and go to next Cell

Try this?
Code:
```Sub condCopy()
Dim LR As Long, i As Long, count As Long
count = 3

For i = 2 To LR
If Sheet1.Range("A" & i).Value = "office No.1" Then
Sheet1.Rows(i).Copy Sheet2.Rows(count)
count = count + 1
End If
Next i
End Sub

```
``` ```

5. ## Re: If condition met copy and go to next Cell

Thanks a lot, but tried it many times and modified it also but no result, i attached the file link below to let the picture more clear ..

http://www.2shared.com/file/3WQ_XYaS/Offices.html

- The file as you can see contains 4 Sheets, the main is Actions ( filled Manual ) and the other 3 is generated automatically but also formatted previously.

- Sheet Named "Office No.1" will check if there anything related to Office No.1 in Sheet "Actions" from C5:C8 regarding Product 1 if found will be copied to Product 1 Area in Sheet "Office No.1" to A5:F5 the yellow area.

- and will continue checking in Product No.1 Area in Actions Sheet if Office No.1 Found again will be copied the data to A6:F6 and so on.

- also all of this will be applied to every product with every Office sheet .

hope everything is more clear now.

appreciated..

6. ## Re: If condition met copy and go to next Cell

This is a bit of challenge for me but hopefully and I'm sure the brilliant Board Members will find a solution for this ..

7. ## Re: If condition met copy and go to next Cell

anybody can help me with that, tried many things but didn't work. please help..

8. ## Re: If condition met copy and go to next Cell

I'm going to to post this again here to be Make it more clear:

- There are 4 Sheets, the main sheet is Actions ( filled Manual ) like below :

Actions

 * A B C D E F G H I J K 2 Product No.1 * * * * * * * * * * 3 Date Action Office Shares Avg Value New Shares Avg New Cost Shares Bought Cost 4 1/1/12 * * * 0 * 0 0 0 * * 5 2/21/12 B Office No.1 396600 20.8 8249280 396600 20.8 8249280 396600 8249280 6 2/21/12 B Office No.2 301000 21.8 6561800 697600 21.23148 14811080 301000 6561800 7 2/21/12 B Office No.3 236990 20.78 4924652.2 934590 21.11699 19735732 236990 4924652 8 2/21/12 S Office No.2 236990 20.78 4924652.2 934590 21.11699 19735732 236990 4924652 9 Total * * 1171580 * * 2963380 * * * * 10 * * * * * * * * * * * 11 Product No.2 * * * * * * * * * * 12 Date Action Office Shares Avg Value New Shares Avg New Cost Shares Bought Cost 13 1/1/12 * * * 0 * 0 0 0 * * 14 2/21/12 B Office No.1 396600 20.8 8249280 396600 20.8 8249280 396600 8249280 15 2/21/12 B Office No.2 301000 21.8 6561800 697600 21.23148 14811080 301000 6561800 16 2/21/12 B Office No.3 236990 20.78 4924652.2 934590 21.11699 19735732 236990 4924652 17 2/21/12 S Office No.2 236990 20.78 4924652.2 934590 21.11699 19735732 236990 4924652 18 Total * * 1171580 * * 2963380 * * * * 19 * * * * * * * * * * * 20 Product No.3 * * * * * * * * * * 21 Date Action Office Shares Avg Value New Shares Avg New Cost Shares Bought Cost 22 1/1/12 * * * 0 * 0 0 0 * * 23 2/21/12 B Office No.1 396600 20.8 8249280 396600 20.8 8249280 396600 8249280 24 2/21/12 B Office No.2 301000 21.8 6561800 697600 21.23148 14811080 301000 6561800 25 2/21/12 B Office No.3 236990 20.78 4924652.2 934590 21.11699 19735732 236990 4924652 26 2/21/12 S Office No.2 236990 20.78 4924652.2 934590 21.11699 19735732 236990 4924652 27 Total * * 1171580 * * 2963380 * * * *

Spreadsheet Formulas
 Cell Formula D9 =SUM(D5:D8) G9 =SUM(G4:G8) D18 =SUM(D14:D17) G18 =SUM(G13:G17) D27 =SUM(D23:D26) G27 =SUM(G22:G26)

Excel tables to the web >> Excel Jeanie HTML 4

and the other 3 ( Office No.1, Office No.2, and Office No.3 ) will generated automatically but also formatted previously like below :

Office No.1

 * A B C D E F G H I J K 2 Product No.1 * * * * * * * * * * 3 Date Action Office Shares Avg Value New Shares Avg New Cost Shares Bought Cost 4 40909 * * * 0 * 0 0 0 * * 5 40960.01 B Office No.1 396600 20.8 8249280 * * * * * 6 * * * * * * * * * * * 7 * * * * * * * * * * * 8 * * * * * * * * * * * 9 Total * * 396600 * * 0 * * * * 10 * * * * * * * * * * * 11 Product No.2 * * * * * * * * * * 12 Date Action Office Shares Avg Value New Shares Avg New Cost Shares Bought Cost 13 40909 * * * 0 * 0 0 0 * * 14 * * * * * * * * * * * 15 * * * * * * * * * * * 16 * * * * * * * * * * * 17 * * * * * * * * * * * 18 Total * * 0 * * 0 * * * * 19 * * * * * * * * * * * 20 Product No.3 * * * * * * * * * * 21 Date Action Office Shares Avg Value New Shares Avg New Cost Shares Bought Cost 22 40909 * * * 0 * 0 0 0 * * 23 * * * * * * * * * * * 24 * * * * * * * * * * * 25 * * * * * * * * * * * 26 * * * * * * * * * * * 27 Total * * 0 * * 0 * * * *

Spreadsheet Formulas
 Cell Formula D9 =SUM(D5:D8) G9 =SUM(G4:G8) D18 =SUM(D14:D17) G18 =SUM(G13:G17) D27 =SUM(D23:D26) G27 =SUM(G22:G26)

Excel tables to the web >> Excel Jeanie HTML 4

- Sheet Named "Office No.1" will check if there anything related to Office No.1 in Sheet "Actions" from C5:C8 regarding Product 1 if found will be copied to Product No.1 Area in Sheet "Office No.1" to A5:F5 the yellow area.

- and will continue checking in Product No.1 Area in Actions Sheet if Office No.1 Found again will be copied the data to A6:F6 and so on.

- also all of this will be applied to every product with every Office sheet .

hope everything is more clear now.

appreciated..

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•