Copy data to another sheet when criteria is matched

[bask1022]

I'm a novice to VB - I have seen several examples close to what I need, but when I try to modify them slightly I end up breaking the function because I just don't know enough of the syntax.

I need to assess data in column N of the first sheet (ToDo) starting with row 5 and going to the last row (which varies depending on the month). Any time the data in N is greater than 0, I need to copy the data in columns A and B to sheet Week1, starting in cell A16 .

Both sheets (ToDo and Week1) are in the same workbook.

Thank you in advance for your help!

 

[SteveO59L]

something along the lines of

Dim c As Range, rng
Dim lr As Long
Dim col_a_val As String
Dim col_b_val As String


Sheets("ToDo").Activate

lr = Range("n" & Rows.Count).End(xlUp).Row
Set rng = Range("n5:n" & lr)
For Each c In rng
If c.Value > 0 Then
col_a_val = c.Offset(0, -13).Value
col_b_val = c.Offset(0, -12).Value
Sheets("Week1").Activate
lr = Range("a" & Rows.Count).End(xlUp).Row + 1
Range("a" & lr) = col_a_val
Range("b" & lr) = col_b_val
Sheets("ToDo").Activate
End If
Next c

 

[bask1022]

SteveO59L - thank you so much for responding.....

I pasted your code into the spreadsheet, and when I try to run it I get a "Invalid outside procedure" error on the line

Sheets("ToDo").Activate

What have I done wrong? Thanks...

something along the lines of

Dim c As Range, rng
Dim lr As Long
Dim col_a_val As String
Dim col_b_val As String


Sheets("ToDo").Activate

lr = Range("n" & Rows.Count).End(xlUp).Row
Set rng = Range("n5:n" & lr)
For Each c In rng
If c.Value > 0 Then
col_a_val = c.Offset(0, -13).Value
col_b_val = c.Offset(0, -12).Value
Sheets("Week1").Activate
lr = Range("a" & Rows.Count).End(xlUp).Row + 1
Range("a" & lr) = col_a_val
Range("b" & lr) = col_b_val
Sheets("ToDo").Activate
End If
Next c

 

[SteveO59L]

is your sheet name ToDo? exactly like that

 

[bask1022]

It is....I have sheets within the workbook: ToDo, Budget, and Wk1

 

[SteveO59L]

drop me a pm with your email addy, and I will send you a sample file

 

[SteveO59L]

did you get the sample file?

 

[bask1022]

I did receive the file - was just out sick for a few days. Thank you - it worked well! Just one question:

I tried to modify the macro to display columns E and N instead of A and B, and though the macro ran, there was no visible data displayed. Might it be because the data in columns E and N is calculated via a formula instead of typed in directly?

Thank you again for your help and patience....