+ Reply to Thread
Results 1 to 6 of 6

Copy cells from each row into new sheet, depending on if statement

Hybrid View

christopherL Copy cells from each row into... 10-28-2013, 05:33 AM
christopherL Re: Copy cells from each row... 10-28-2013, 07:13 AM
JOHN H. DAVIS Re: Copy cells from each row... 10-28-2013, 07:21 AM
christopherL Re: Copy cells from each row... 10-28-2013, 07:33 AM
JOHN H. DAVIS Re: Copy cells from each row... 10-28-2013, 08:06 AM
djdjdj Re: Copy cells from each row... 10-28-2013, 07:19 AM
  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2002
    Posts
    71

    Copy cells from each row into new sheet, depending on if statement

    Hi there,
    currently I have this macro that just changes the background color of the cell I am investigating.
    Instead of marking the cells that fill the if-statement, I want it, for each row that fulfill the if-statement, copy cell A and L into a new sheet.

    So if L is > higher then input_box, then copy that value from cell L for that row along with the value from cell A for that row.

    Current code

    Sub Check_MaxMinOver()
    Dim input_box As Integer
    
    
    input_box = InputBox("What should maxmin be higher than?")
    
    For Each cell In Range("L5:L" & Cells(Rows.Count, "L").End(xlUp).Row)
        If cell.Value > input_box Then
            cell.Select
                    With Selection.Interior
                    .ColorIndex = 7
                    End With
        End If
    Next
    End Sub

  2. #2
    Registered User
    Join Date
    10-11-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2002
    Posts
    71

    Re: Copy cells from each row into new sheet, depending on if statement

    Isn't there anyone that knows how to solve this? :p

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells from each row into new sheet, depending on if statement

    Maybe:

    Sub christopherLzz()
    Dim input_box As Integer
    Dim cell As Range
    input_box = InputBox("What should maxmin be higher than?")
    
    For Each cell In Range("L5:L" & Cells(Rows.count, "L").End(xlUp).Row)
        If cell.Value > input_box Then
            Range(cell.Offset(, -11), cell).Copy Sheets("new sheet").Range("A" & Rows.count).End(3)(2)
        End If
    Next
    End Sub

  4. #4
    Registered User
    Join Date
    10-11-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2002
    Posts
    71

    Re: Copy cells from each row into new sheet, depending on if statement

    Worked after I noticed I had to name it to my own sheet :P
    Quote Originally Posted by JOHN H. DAVIS View Post
    Maybe:

    Sub christopherLzz()
    Dim input_box As Integer
    Dim cell As Range
    input_box = InputBox("What should maxmin be higher than?")
    
    For Each cell In Range("L5:L" & Cells(Rows.count, "L").End(xlUp).Row)
        If cell.Value > input_box Then
            Range(cell.Offset(, -11), cell).Copy Sheets("new sheet").Range("A" & Rows.count).End(3)(2)
        End If
    Next
    End Sub
    Last edited by christopherL; 10-28-2013 at 07:55 AM.

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells from each row into new sheet, depending on if statement

    Glad to hear that. Thanks for the feedback.

  6. #6
    Forum Contributor
    Join Date
    04-25-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: Copy cells from each row into new sheet, depending on if statement

    Creates a new sheet and names it according to cell value in column L

    Sub Check_MaxMinOver()
    Dim input_box As Integer
    
    input_box = InputBox("What should maxmin be higher than?")
    
    For Each cell In Range("L5:L" & Cells(Rows.Count, "L").End(xlUp).Row)
        If cell.Value > input_box Then
            Sheets.Add.Name = cell.Text
                Sheets(cell.Text).Range("L1").Value = cell.Value
                Sheets(cell.Text).Range("A1").Value = cell.Offset(0, -11).Value
        End If
    Next
    End Sub

+ 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 cells from another sheet depending on category
    By Lemac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2013, 06:47 PM
  2. [SOLVED] Copy row data from one sheet to another depending on a category
    By Burli in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-24-2013, 10:13 AM
  3. Copy Sheet to new Workbook depending on Sheet name or value in A1
    By anauros in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-19-2012, 08:31 AM
  4. Copy shells to another sheet, depending on predefined row
    By Topster75 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-30-2009, 08:31 AM
  5. copy row depending on value to new sheet
    By jonpdavies@gmail.com in forum Excel General
    Replies: 5
    Last Post: 09-28-2005, 01:05 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