Solved

copy specific rows from one tab to another tab with VBA code

Posted on 2013-11-01
2
252 Views
Last Modified: 2013-11-01
I have attached a spread sheet which demonstrates what I need to do which is:

Sheet 1 and Sheet 2 start out with the exact same details.

Sheet2 is updated with ‘New, amended or deleted’ details ad this is highlighted by the user in column ‘N’

I need my macro to:
1]  review sheet 2 against sheet 1 and where any new items are on Sheet 2 put those lines onto a new tab called ‘New’ [assume this tab did not exist only sheets 1 and 2]
2] copy any items from Sheet 2 that have been amended onto a new tab called Amend_Delete

Appreciate any assistance with this
Thanks
NAD.xls
0
Comment
Question by:Jagwarman
2 Comments
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39617851
Use the following macro

Sub ReviewData()
Dim I As Integer, DBWS As Worksheet, WS As Worksheet, NewWS As Worksheet, ADWS As Worksheet
Dim NewI As Integer, ADI As Integer

Sheets.Add.Name = "New"
Sheets.Add.Name = "Amend_Delete"

Set DBWS = Sheets("Sheet1")
Set WS = Sheets("Sheet2")
Set NewWS = Sheets("New")
Set ADWS = Sheets("Amend_Delete")

NewWS.Rows(1).Value = DBWS.Rows(1).Value
ADWS.Rows(1).Value = DBWS.Rows(1).Value

For I = 2 To WS.Cells(Rows.Count, 1).End(xlUp).Row
    If WS.Cells(I, 14) = "" Then
        Else
            If WS.Cells(I, 14) = "New" Then
                NewI = NewWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
                NewWS.Rows(NewI).Value = WS.Rows(I).Value
            Else
                If WS.Cells(I, 14) = "Amended" Then
                    ADI = ADWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
                    ADWS.Rows(ADI).Value = WS.Rows(I).Value
                Else
                    If WS.Cells(I, 14) = "Deleted" Then
                        ADI = ADWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
                        ADWS.Rows(ADI).Value = WS.Rows(I).Value
                    End If
                End If
            End If
    End If
Next
NewWS.Cells.Font.Size = 8
ADWS.Cells.Font.Size = 8
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 39617879
Brilliant thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 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…
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…

821 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