Solved

Need Serious Help  Copying and Pasting Rows...  Where are the really smart guys..?

Posted on 2007-03-26
3
201 Views
Last Modified: 2013-11-25
have a function which, upon inserting a date into sheet "Status Report", all other sheets in the work book, in column A:A are searched for that date and those rows are then copied over to another sheet, "hidden data."  This much works perfect, and the function code is:

Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Address <> [G3].Address Then Exit Sub
          If Not IsDate(Target.Value) Then
               MsgBox "Value entered into " & Target.Address & " is not a date"
               Exit Sub
          End If
          If Target.Value > 0 Then
            Sheets("Hidden Data").Range("A1").Value = Target.Value
            Dim ws As Worksheet
            Dim fnd As Range
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            For Each ws In ThisWorkbook.Worksheets
                 If ws.Name <> "Status Report" And ws.Name <> "Hidden Data" Then
                      ws.Activate
                      Set fnd = ws.Range("A:A").Find(Target.Value, , xlValues, xlWhole)
                      If Not fnd Is Nothing Then
                        fnd.EntireRow.Select
                           fnd.EntireRow.Copy
                           Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                      End If
                 End If
            Next
          End If
          Sheets("Status Report").Activate
          Application.ScreenUpdating = True
          Application.EnableEvents = True
     End Sub

What I'm trying to do, if possible, is for this code to, in plain terms, find the date on the sheets and copy the row, just as it's doing now, but if the function hits a sheet that does not have that date present, a row on the output sheet, "Hidden Data" is skipped and then the next queried, the row with the correct date goes in the next row and so on and so forth.

So basically, if I enter 3/22/2007 on the "Status Report" Sheet, the first sheet queried has the date, so this row is copied and pasted on row 2 of the "Hidden Data" sheet.  The second sheet in line to be queried, does not have an entry for that date, so on the "Hidden Data" sheet a row is skipped, thus leaving row 3 empty, and then the next sheet, having the appropriate date, the row is copied and pasted into row 4 and so on.  I know very near nothing, a bit I've picked up from here about VB, so please keep this in mind when responding, I'll need perhaps more than your average response.

I also have a need to conduct possibly a second query, that is, if a queried sheet has more than one entry for the same date, a second column, "time" is looked at and the row with the more recent time is the one copied over to the "Hidden Data" sheet.  This second have is probably better for another question, but since the same bit of code is what's going to have to change, I figured I'd put it here in case its an easy fix and tha perhaps in looking at the skip a row problem, one might also see a quick fix for this second dillemma.

Thank you all so much in advance, I've learned much on here, and am probably getting up there to winning the "most asked questions" award, if one existed that is.

Joe
0
Comment
Question by:josepharichard
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
WJReid earned 500 total points
ID: 18797993
HI,

Try:
Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Address <> [G3].Address Then Exit Sub
          If Not IsDate(Target.Value) Then
               MsgBox "Value entered into " & Target.Address & " is not a date"
               Exit Sub
          End If
          If Target.Value > 0 Then
            Sheets("Hidden Data").Range("A1").Value = Target.Value
            Dim ws As Worksheet
            Dim fnd As Range
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            For Each ws In ThisWorkbook.Worksheets
                 If ws.Name <> "Status Report" And ws.Name <> "Hidden Data" Then
                      ws.Activate
                      Set fnd = ws.Range("A:A").Find(Target.Value, , xlValues, xlWhole)
                      If fnd Is Nothing Then
                        Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = "'"
                      End If
                      If Not fnd Is Nothing Then
                        fnd.EntireRow.Select
                           fnd.EntireRow.Copy
                           Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                      End If
                 End If
            Next
          End If
          Sheets("Status Report").Activate
          Application.ScreenUpdating = True
          Application.EnableEvents = True
     End Sub
0
 

Author Comment

by:josepharichard
ID: 18799516
Ahh, thank you, perfect, any idea on the duplicate entry issue, or should I post that as a new question?
0
 

Author Comment

by:josepharichard
ID: 18801443
Posted as new question:  http:Q_22475445.html
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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

919 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

15 Experts available now in Live!

Get 1:1 Help Now