Results 1 to 4 of 4

Problem with macro intended to copy cells to a different worksheet based on cell value

Threaded View

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    6

    Problem with macro intended to copy cells to a different worksheet based on cell value

    Hi there,

    Below is part of a macro that I've partly written and partly based off other threads/advice provided here.

    It's intention is to identify which row has one of 18 possible values in it's 'H' column and then copy colums A:H of that row into the range A86:H86 in a seperate spreadsheet. A subsequent part of the macro then copies A86:H86 to another part of the spreadsheet, but that is tested and working fine, so I've left it out for the sake of brevity.

    The issue I am having is with the line "Then Range(Intersect(Rows(i), Range("A:H"))).Copy Sheets("Lists & Formulas").Range("A86:H86"). When the macro runs it returns an error 'Run-time error '1004': Method 'Range' of object '_Global' failed.

    Dim i As Long, nrow As Long
        With Worksheets("Work on Hand")
                Application.Goto Reference:="name1"
                nrow = .Range("A" & ActiveCell.Row).End(xlUp).Row
                For i = 4 To nrow
                    If .Range("H" & i).Value = "value1" _
                    Or .Range("H" & i).Value = "value2" _
                    Or .Range("H" & i).Value = "value3" _
                    Or .Range("H" & i).Value = "value4" _
                    Or .Range("H" & i).Value = "value5" _
                    Or .Range("H" & i).Value = "value6" _
                    Or .Range("H" & i).Value = "value7" _
                    Or .Range("H" & i).Value = "value8" _
                    Or .Range("H" & i).Value = "value9" _
                    Or .Range("H" & i).Value = "value10" _
                    Or .Range("H" & i).Value = "value11" _
                    Or .Range("H" & i).Value = "value12" _
                    Or .Range("H" & i).Value = "value13" _
                    Or .Range("H" & i).Value = "value14" _
                    Or .Range("H" & i).Value = "value15" _
                    Or .Range("H" & i).Value = "value16" _
                    Or .Range("H" & i).Value = "value17" _
                    Or .Range("H" & i).Value = "value18" _
                    Then Range(Intersect(Rows(i), Range("A:H"))).Copy Sheets("Lists & Formulas").Range("A86:H86")
                    Range("A" & i).EntireRow.Delete
                Next i
            End With
    Last edited by Crangiopharengoma; 08-15-2013 at 03:06 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help with Macro to Copy/Paste cells to different worksheet based on Value
    By BehrBrew in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-25-2013, 06:41 AM
  2. [SOLVED] Copy specific cells based on one word in the cell, to a different worksheet
    By Lfaulst1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2013, 05:26 PM
  3. Macro causing Excel to freeze - intended to hide cells with zero value
    By ParkingExcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-26-2012, 02:11 AM
  4. [SOLVED] Macro to copy certain cells to new worksheet based on cell value
    By mmctague in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-28-2012, 10:54 AM
  5. Macro Problem, copy range of cells based on criteria
    By Ang12345 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2010, 05:10 PM

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