vba copy and paste based on multiple loop criteria

**sarahcpa** · 04-10-2013, 06:11 PM

Hello I really need some help with my coding in Excel 2007.
I have 80 worksheets in my workbook. I am trying to evaluate each sheet so that if there is a "Y" in a certain range of worksheets ("M" for Monday), then that sheet's data will be pasted to the sheet that begins with P (for prior week). There are 10 of these PW1, PW2, etc.

Dim ws  As Worksheet
Dim ws1 As Worksheet

Application.ScreenUpdating = False


For Each ws In ActiveWorkbook.Worksheets
    If Left(ws.Name, 1) = "M" And ws.Range("B2").Value = "Y" Then   'evaluate if yes
        ws.Range("A4:R37").Copy
        ElseIf Left(ws1.Name, 1) = "P" And ws1.Range("A4").Value Is Null Then
        ws1.Range("a4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    End If
Next ws
    Sheets("108 Compare").Select
    Range("A81").Select

Application.ScreenUpdating = True

the code fails on the ElseIf line.

**rylo** · 04-10-2013, 07:38 PM

Hi

ws1 is not assigned anywhere.

rylo

**sarahcpa** · 04-18-2013, 01:49 PM

Well Rylo, I'm not sure what you mean. I have ws1 setup in the Dim statement and the Elseif line. Do you have any suggestions as to how else to assign it?

**Leith Ross** · 04-18-2013, 01:57 PM

Hello sarahcpa,

Welcome to the Forum!

The statement Dim ws1 As Worksheet creates a Worksheet Object variable. You must assign that object variable to an existing worksheet.

**sarahcpa** · 04-18-2013, 07:09 PM

I'm still stumped. Tried to change the code up a bit, but now I'm getting "end if without block If" error.
pleeeeeease someone help me

you guys seem to think I know what I'm doing.

'Application.ScreenUpdating = False

Dim ws, Dest  As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("B2").Value = "Y" Then   'evaluate if it is a carryover
        ws.Range("A4:R37").Copy
        For Each Dest In ActiveWorkbook.Worksheets
            If Left(Dest.Name, 2) = "PW" And ws1.Range("A4").Value Is Null Then
                Dest.Range("A4").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                  :=False, Transpose:=False
                Application.CutCopyMode = False
                Exit For
            End If
    End If
Next ws
    
    Sheets("108 Compare").Select
    Range("A81").Select

'Application.ScreenUpdating = True

End Sub

**rylo** · 04-18-2013, 07:37 PM

Hi

Between your 2 end if statements, put in a next Dest. You haven't completed the for loop.

rylo

**sarahcpa** · 04-19-2013, 10:29 AM

YAY
between your comments and more testing of individual pieces, I finally got it to work

Application.ScreenUpdating = False

' Loop through all sheets in the workbook.
      For i = 1 To Sheets.Count
         Sheets(i).Unprotect
      Next i


Dim ws, Dest  As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("B2").Value = "Y" Then   'evaluate if it is a carryover
        ws.Range("A4:R37").Copy
        For Each Dest In ActiveWorkbook.Worksheets
            If Left(Dest.Name, 2) = "PW" And IsEmpty(Dest.Range("A4").Value) Then
                Dest.Range("A4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
                Application.CutCopyMode = False
                Exit For
            End If
        Next Dest
    End If
Next ws