Results 1 to 7 of 7

vba copy and paste based on multiple loop criteria

Threaded View

  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    Little Rock, Ar
    MS-Off Ver
    Excel 2007
    Posts
    4

    vba copy and paste based on multiple loop criteria

    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.
    Last edited by Leith Ross; 04-18-2013 at 01:54 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1