+ Reply to Thread
Results 1 to 2 of 2

Having problem to copy the data to different worksheet once condition met

Hybrid View

  1. #1
    Registered User
    Join Date
    01-14-2015
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    1

    Having problem to copy the data to different worksheet once condition met

    I'm trying to scan through the worksheet and if the worksheet "Testing1" the "status" column showed "Closed" then copy the entire row to worksheet "Sheet2" and paste it there with similar header. i keep getting subscript over range message.

    Sub MainMacro()


    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    Set ws1 = Sheets("Testing1")
    Set ws2 = Sheets("Sheet2")

    Dim lastrow As Long, lastcol As Long, icol As Long
    Dim icell As Range

    lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    lastcol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

    For icol = 1 To lastcol
    For Each icell In ws1.Range(ws1.Cells(2, icol), ws1.Cells(lastrow, icol))
    If icell.Value = "Closed" Then
    icell.EntireRow.Copy _
    Destination:=Sheets(ws2.Cells(2, icol).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
    Next icell
    Next icol

    End Sub

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Having problem to copy the data to different worksheet once condition met

    Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    Edit your original post.


    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy data from one worksheet to another worksheet ( if meets condition) ????
    By Rmag in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2014, 06:48 PM
  2. Replies: 10
    Last Post: 06-06-2013, 12:10 AM
  3. Copy data from one worksheet to another with condition
    By vkt_1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-29-2012, 12:07 AM
  4. Replies: 1
    Last Post: 05-12-2009, 03:18 AM
  5. [SOLVED] copy data from 1 worksheet to another based on a condition
    By vinisunodh@gmail.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2006, 08:20 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