If Statement will not copy the results to a seperate page

Question

Creating an If statement, to sort out my data based on the month in a certain column. The code is as such

Worksheets("Project Info").Activate

Dim month As Long
Application.ScreenUpdating = False
For month = Cells(Rows.Count, 23).End(xlUp).Row To 1 Step -1
    If InStr(1, Cells(month, 23).Value Like "1/*", 1) _
    Then Cells(month, 23).EntireRow.Copy
    Worksheets("January").Activate
    ActiveSheet.Paste
Next month

For some reason, it won't copy data into the specified page. It doesn't even actually copy the data at all. Any ideas?

You should use sheet references throughout. Once you activate the January sheet your code will refer to that as it is the active sheet. — SJR, Mar 13 '19 at 12:00
@SFarrell But **how** did you try that? *"no avail"* doesn't help us to understand where your difficulties were. Please [edit] your question and update the code. As SJR pointed out the code cannot work as expected as shown in the question. The following might help: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Mar 13 '19 at 12:20
I have updated it. I was using activate sheet any time I was changing sheets, to ensure the correct one was selected. — S Farrell, Mar 13 '19 at 12:23
That change does not address my point. However, the answer might. — SJR, Mar 13 '19 at 13:19
You are basically using the single line syntax. Add `End If` at the end of your code block. — Sirmyself, Mar 13 '19 at 13:30

Darrell H · Accepted Answer · 2019-03-13T13:28:18.763

2

As mentioned in the comments above, you need to use sheet references otherwise you are going to jump back and forth between sheets and after the first iteration you are copying and pasting to the same sheet. You also have to specify where you are going to paste the data and change the destination each time, otherwise you will keep pasting over the same data.

Dim ws1 as Worksheet
Dim ws2 as Worksheet

Set ws1 = Worksheets("Project Info")
Set ws2 = Worksheets("January")

Dim month As Long
Dim t as Long

Application.ScreenUpdating = False

t=1
For month = ws1.Cells(Rows.Count, 23).End(xlUp).Row To 1 Step -1
    If InStr(1, ws1.Cells(month, 23).Value Like "1/*", 1) Then
       ws1.Cells(month, 23).EntireRow.Copy
       ws2.Rows(t).PasteSpecial xlPasteAll
       t=t+1
    End if
Next month

edited Mar 13 '19 at 13:28

answered Mar 13 '19 at 13:17

Darrell H

1,876
1
9
14

Note this copies only if the `If` statement is true, but it pastes **always** because your `If` statement is a one-liner and has no `End If`. Which the OP probably meant to do. – Pᴇʜ Mar 13 '19 at 13:25
Just another thing: `Month` is already a function in VBA ([Month function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/month-function)) and should therefore be forbidden (highly not recommended) as a variable name. – Pᴇʜ Mar 13 '19 at 13:31

If Statement will not copy the results to a seperate page

1 Answers1