Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-01
2
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

722 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