Results 1 to 10 of 10

Debug my loop!

Threaded View

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    United States
    MS-Off Ver
    2010
    Posts
    41

    Debug my loop!

    The attached code works perfectly for the first sheet, but on the second i get error "1004: Method 'Range' of object'_Worksheet' failed". I've attached an example of what the data looks like that i'm working with. We have a spreadsheet for every customer. I'm trying to copy the information into one spreadsheet in a database format. Since the customers may be located in different locations i'm trying to separate the information between the three regions.

    Sub Compile2()
    
    
        Dim sh As Worksheet
        Dim UKsh As Worksheet
        Dim EUsh As Worksheet
        Dim USsh As Worksheet
        Dim EUrow As Long, UKrow As Long, USrow As Long
        Dim FinNum As Integer
    
        EUrow = 2
        UKrow = 2
        USrow = 2
    
    Set UKsh = Sheets("UK")
    Set EUsh = Sheets("EU")
    Set USsh = Sheets("US")
    
    
    For Each sh In Worksheets
        With sh
        FinNum = 3
            Do
                If .Range("A60").Value = "EU" Then
                    .Range("B5").Copy
                    EUsh.Range("A" & EUrow).PasteSpecial Transpose:=True
                    .Range(Cells(8, FinNum), Cells(48, FinNum)).Copy
                    EUsh.Range("B" & EUrow).PasteSpecial Transpose:=True
                    EUrow = EUrow + 1
                ElseIf .Range("A60").Value = "UK" Then
                    .Range("B5").Copy
                    UKsh.Range("A" & UKrow).PasteSpecial Transpose:=True
                    .Range(Cells(8, FinNum), Cells(48, FinNum)).Copy
                    UKsh.Range("B" & UKrow).PasteSpecial Transpose:=True
                    UKrow = UKrow + 1
                Else
                    .Range("B5").Copy
                    USsh.Range("A" & USrow).PasteSpecial Transpose:=True
                    .Range(Cells(8, FinNum), Cells(48, FinNum)).Copy
                    USsh.Range("B" & USrow).PasteSpecial Transpose:=True
                    USrow = USrow + 1
                End If
                FinNum = FinNum + 1
             Do Until FinNum > 7
        Application.CutCopyMode = False
        End With
    Next
    End Sub
    Compile example.xlsm

    Edit: I just tested it again, and now it is saying that I have a compile error "End With without With". So I have even more problems
    Last edited by sjennings007; 12-24-2015 at 12:38 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Loop Sub until debug
    By drewkenna in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2015, 09:13 AM
  2. Pause and debug at a particular point in a loop
    By ualdriver in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2015, 12:12 AM
  3. VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode
    By valerie.k.chiang in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2014, 03:48 PM
  4. [SOLVED] Debug: How to debug this code?
    By reach78 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-25-2013, 07:16 AM
  5. How do debug a loop?
    By max3732 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2013, 04:27 PM
  6. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  7. Debug this?
    By hke in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-11-2005, 11:31 AM

Tags for this Thread

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