Solved

VBA copy rows with certain values

Posted on 2012-03-13
3
438 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

12 Experts available now in Live!

Get 1:1 Help Now