Solved

VBA copy rows with certain values

Posted on 2012-03-13
3
440 Views
Last Modified: 2012-03-13
I would like to copy only certain rows to a new worksheet in the same workbook using VBA.

Copy Row 1 - header row

Copy each row whose "On Hand" number equals 1.

See attached sample worksheet.

ScreenshotBook1.xls
0
Comment
Question by:mcnuttlaw
  • 2
3 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37717403
Option Explicit

Sub copyOnHand()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksNew As Worksheet
Dim rngOut As Range
Dim rng As Range
Dim r As Range
Dim i As Long

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    Set rng = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
    
    Set wksNew = wkb.Worksheets.Add(after:=wkb.Worksheets(wkb.Worksheets.Count))
    
    wksNew.Range("A1").Resize(, 3).Value = wks.Range("A1:C1").Value
    
    Set rngOut = wksNew.Range("A2:C2")
    
    For Each r In rng
        If r.Offset(0, 2).Value > 0 Then
            rngOut.Offset(i).Value = r.Resize(, 3).Value
            i = i + 1
        End If
    Next r
            
End Sub

Open in new window


See attached.

Dave
Book1.xls
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37717486
Oops.  I forgot to mention that the new worksheet is actually an existing one titled "End Result".

Pressing the button should replace the existing contents of "End Result".
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37717533
Option Explicit

Sub copyOnHand()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksNew As Worksheet
Dim rngOut As Range
Dim rng As Range
Dim r As Range
Dim i As Long

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    Set rng = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
    
    On Error Resume Next
    Set wksNew = wkb.Worksheets("End Result")
    If Err.Number <> 0 Then
        Set wksNew = wkb.Worksheets.Add(after:=wkb.Worksheets(wkb.Worksheets.Count))
        wksNew.Name = "End Result"
    End If
    
    wksNew.Cells.Clear
    
    wksNew.Range("A1").Resize(, 3).Value = wks.Range("A1:C1").Value
    
    Set rngOut = wksNew.Range("A2:C2")
    
    For Each r In rng
        If r.Offset(0, 2).Value > 0 Then
            rngOut.Offset(i).Value = r.Resize(, 3).Value
            i = i + 1
        End If
    Next r
            
    wks.Activate
End Sub

Open in new window


Ok.  see attached.

Dave
Book1.xls
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now