Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-11-01
2
Medium Priority
?
266 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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

783 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