+ Reply to Thread
Results 1 to 3 of 3

Copy rows based on criteria to new sheet, controll the destination range.

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-05-2008
    Location
    Kiruna
    Posts
    163

    Copy rows based on criteria to new sheet, controll the destination range.

    Hi

    I have this code to copy rows from sheet "data" to sheet "översikt".


    Private Sub CommandButton1_Click()
    Range("A2:E100").Clear
    
    
    
    
    
    
    Dim i As Integer
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Data")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Översikt")
    
    For i = 3 To ws1.Range("A65536").End(xlUp).Row
        If ws1.Cells(i, 3) > 0 Or ws1.Cells(i, 5) > 0 Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
        
    Next i
    
    Blad1.Range("E2:E100").Font.Italic = True
    Blad1.Range("A1:AF100").Interior.ColorIndex = 2
    
    End Sub
    Everything works fine exept i cant figure how to controll wich line it starts to paste from.

    Right now i have a header on Row1 and it starts to paste in row 2.
    I want to be able to choose with row it should start from like row 4 or 5, wich section of the code controlls that ?

    Petter

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy rows based on criteria to new sheet, controll the destination range.

    Option Explicit
    
    Sub CommandButton1_Click()
    Range("A2:E100").Clear
    
    Dim i As Integer, Myrow As Long
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Data")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Översikt")
    Myrow = 4 'change this to suite you
    For i = 3 To ws1.Range("A65536").End(xlUp).Row
        If ws1.Cells(i, 3) > 0 Or ws1.Cells(i, 5) > 0 Then ws1.Rows(i).Copy ws2.Cells(Myrow, 2)
        Myrow = Myrow + 1
    Next i
    
    Blad1.Range("E2:E100").Font.Italic = True
    Blad1.Range("A1:AF100").Interior.ColorIndex = 2
    
    End Sub

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy rows based on criteria to new sheet, controll the destination range.

    Option Explicit
    
    Sub CommandButton1_Click()
    Range("A2:E100").Clear
    
    Dim i As Integer, Myrow As Long
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Data")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Översikt")
    Myrow = 4 'change this to suite you
    For i = 3 To ws1.Range("A65536").End(xlUp).Row
        If ws1.Cells(i, 3) > 0 Or ws1.Cells(i, 5) > 0 Then ws1.Rows(i).Copy ws2.Cells(Myrow, 1)
        Myrow = Myrow + 1
    Next i
    
    Blad1.Range("E2:E100").Font.Italic = True
    Blad1.Range("A1:AF100").Interior.ColorIndex = 2
    
    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 and Paste Rows based on Criteria onto another sheet and sort based on oldest item
    By Kushal8684 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2013, 05:37 AM
  2. Copy rows to a given sheet based on several criteria
    By bfora in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-02-2013, 11:12 AM
  3. Replies: 4
    Last Post: 07-24-2012, 07:09 AM
  4. Copy Rows from Sheet 1 based on Criteria
    By ppeter01 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2008, 05:18 PM
  5. Copy Rows In New Sheet Based On Date Range Criteria
    By ahmedwaseem2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2006, 05:45 AM

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