Need Serious Help Copying and Pasting Rows... Where are the really smart guys..?
Posted on 2007-03-26
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"
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
Set fnd = ws.Range("A:A").Find(Target.Value, , xlValues, xlWhole)
If Not fnd Is Nothing Then
Sheets("Hidden Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
Application.EnableEvents = True
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.