copying information and pasting in another sheet

[Roooster]

What I am trying to do: Find where a row has the name "Craig Bethune" in it then select the corresponding cells in column M,O,Q,S and U (this will open a "new sheet") Copy the data that is opened and paste it in the next open row in a sheet called "Prime Broker"

My Problem: It seems like the macro is skipping over some cells in the corresponding columns with a number greater than 0.

Another wierd thing is it starts at a15 instead of a1 on the prime broker sheet.

Here is my Macro code, including the creation of the new sheet

Sub Problem()
'
'select daily summary tab and add new sheet
'
'
Sheets("Daily Summary by Value Date").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Prime Broker"
Sheets("Daily Summary by Value Date").Select
'
'copy and paste info from daily values into Prime Broker sheet
'
'
Dim cell As Range, area As Range, x As Integer
x = 0

Set area = Sheets("Daily Summary by Value Date").Range("l1:l250")
For Each cell In area
If cell.Value = "Craig Bethune" Then
'Cell M
If cell.Offset(0, 13) > 0 Then
Sheets("Daily Summary by Value Date").Select
cell.Offset(0, 13).Select
'x = x + cell.Offset(0, 13)
Selection.ShowDetail = True
Selection.Copy
Sheets("Prime Broker").Select
Range("a1").Offset(1, 0).Select
ActiveSheet.Paste
End If
'cell O
If cell.Offset(0, 15) > 0 Then
Sheets("Daily Summary by Value Date").Select
cell.Offset(0, 15).Select
'x = x + cell.Offset(0, 15)
Selection.ShowDetail = True
Selection.Copy
Sheets("Prime Broker").Select
Range("a1").Offset(x, 0).Select
ActiveSheet.Paste
End If
'cell Q
If cell.Offset(0, 17) > 0 Then
Sheets("Daily Summary by Value Date").Select
cell.Offset(0, 17).Select
'x = x + cell.Offset(0, 17)
Selection.ShowDetail = True
Selection.Copy
Sheets("Prime Broker").Select
Range("a1").Offset(x, 0).Select
ActiveSheet.Paste
End If
'cell S
If cell.Offset(0, 19) > 0 Then
Sheets("Daily Summary by Value Date").Select
cell.Offset(0, 19).Select
'x = x + cell.Offset(0, 19)
Selection.ShowDetail = True
Selection.Copy
Sheets("Prime Broker").Select
Range("a1").Offset(x, 0).Select
ActiveSheet.Paste
End If
'cell U
If cell.Offset(0, 21) > 0 Then
Sheets("Daily Summary by Value Date").Select
cell.Offset(0, 21).Select
'x = x + cell.Offset(0, 21)
Selection.ShowDetail = True
Selection.Copy
Sheets("Prime Broker").Select
Range("a1").Offset(x, 0).Select
ActiveSheet.Paste
End If
'x = x + 1
End If
Next cell

End Sub


any help would be greatly appreciated

 

[wigi]

My Problem: It seems like the macro is skipping over some cells in the corresponding columns with a number greater than 0.

Well, what would you expect with code like...

For Each cell In area
    If cell.Value = "Craig Bethune" Then
'Cell M
    If cell.Offset(0, 13) > 0 Then
        Sheets("Daily Summary by Value Date").Select
        cell.Offset(0, 13).Select
        'x = x + cell.Offset(0, 13)
        Selection.ShowDetail = True
        Selection.Copy
        Sheets("Prime Broker").Select
        Range("a1").Offset(1, 0).Select
        ActiveSheet.Paste
    End If

Did you use F8 to step through the code lines and see what happens, 1 line at the time?

PS: Please use code tags when you paste VBA code on the forum.
To add code tags, you should type in your post:

then paste your code, and lastly, type:

['/code]

WITHOUT the ' in front of the /

Thanks for the consideration.

 

[Roooster]

I was unaware I could do that with f8 and the code tags. I will use both of those in the future.

As for now I am kinda shooting in the dark by searching the Internet and what I remember of logic from visual basic.

Thanks again for the help!

 

[Roooster]

F8 doesn't tell me what each line of code is doing. If it matters I'm working on excel 2k3.

Could you tell me how to select a cell in column m when it finds the name Craig Bethune in column l?

 

[wigi]

Your variable called cell represents cells in column I. If you use cell.Offset(0, 4), you go 4 cells to the right of that cell, so effectively the cell on the same row but in column M. With that knowledge, you can now work on the "new" cell. For instance, checking the contents of that cell using cell.Offset(0, 4).Value

 

[Roooster]

Next question, I am so close. Does excel count hidden cells when going over on the offset?

Ex: say I am going to column M it's (0,13) if 4 columns are hidden before M would it be (0, 9)?

 

[wigi]

No, they count as well.

 

[Roooster]

That's very odd then, there are a total of 17 cells it should be selecting with a total of 538 records. I'm getting 6 cells opened and 334 records.

 

[wigi]

That's very odd then, there are a total of 17 cells it should be selecting with a total of 538 records. I'm getting 6 cells opened and 334 records.

Then there must be something else going on AFAIK.

 

[Roooster]

Let me start by saying you have been a huge help! I have got this almost working!

Now it is pasting over some of the records it has already pasted. I have tried to add extra rows in, but it doesn't seem to work. Here is my code

Dim cell As Range, area As Range, x As Integer
x = 0

Set area = Sheets("Daily Summary by Value Date").Range("d1:d250")
For Each cell In area
    If cell.Value = "Craig Bethune" Then
'Cell M
    If cell.Offset(0, 9).Value > 0 Then
        x = x + cell.Offset(0, 9).Value + 2
        Sheets("Daily Summary by Value Date").Select
        cell.Offset(0, 9).Select
        Selection.ShowDetail = True
        Selection.Copy
        Sheets("Prime Broker").Select
        Range("a1").Offset(1, 0).Select
        ActiveSheet.Paste
        Sheets("Daily Summary by Value Date").Select
    End If
'cell O
    If cell.Offset(0, 11).Value > 0 Then
        x = x + cell.Offset(0, 11).Value + 2
        cell.Offset(0, 11).Select
        Selection.ShowDetail = True
        Selection.Copy
        Sheets("Prime Broker").Select
        Range("a1").Offset(x, 0).Select
        ActiveSheet.Paste
        Sheets("Daily Summary by Value Date").Select
    End If
'cell Q
    If cell.Offset(0, 13).Value > 0 Then
        x = x + cell.Offset(0, 13).Value + 2
        cell.Offset(0, 13).Select
        Selection.ShowDetail = True
        Selection.Copy
        Sheets("Prime Broker").Select
        Range("a1").Offset(x, 0).Select
        ActiveSheet.Paste
        Sheets("Daily Summary by Value Date").Select
    End If
'cell S
    If cell.Offset(0, 15).Value > 0 Then
        x = x + cell.Offset(0, 15).Value + 2
        cell.Offset(0, 15).Select
        Selection.ShowDetail = True
        Selection.Copy
        Sheets("Prime Broker").Select
        Range("a1").Offset(x, 0).Select
        ActiveSheet.Paste
        Sheets("Daily Summary by Value Date").Select
    End If
'cell U
    If cell.Offset(0, 17).Value > 0 Then
        x = x + cell.Offset(0, 17).Value + 2
        cell.Offset(0, 17).Select
        Selection.ShowDetail = True
        Selection.Copy
        Sheets("Prime Broker").Select
        Range("a1").Offset(x, 0).Select
        ActiveSheet.Paste
        Sheets("Daily Summary by Value Date").Select
        x = x + cell.Offset(0, 17).Value + 2
     End If
     
    End If
Next cell

So somethign is wrong with my count, I think.