MrExcel Publishing
Your One Stop for Excel Tips & Solutions

transferring info


Posted by amy on October 30, 2001 12:56 PM

i was wondering if there was a way to transfer a whole row of data (covering several columns) from one sheet to another automatically. If column a, row a has any variable (it is not blank), i would like to transfer all of row a's information from sheet 1 to sheet 2. subsequently, if column a, row g has a variable (not the same one as before though), i would also like to transfer that whole row g to sheet 2. is this doable.


Posted by Barrie Davidson on October 30, 2001 1:21 PM

What rows do you want to copy from Sheet1 and where, in Sheet2, do you want to paste the data? What variable in row "g" will determine whether it is copied?

Regards,
BarrieBarrie Davidson

Posted by amy on October 30, 2001 1:34 PM

i want to single out which reports are due, so the variable in row g would be a date. i want to take the whole row of information (information on a single report) on sheet 1 if it is due, and transfer that to a sheet that would only have listings for 'reports that are due'. my other report listings do not have a due date.

ideally, sheet 1 would contain every report that has been received, and sheet 2 would contain every report that has a due date.

hope this helps, thank you.
amy

Posted by Barrie Davidson on October 30, 2001 1:39 PM

Amy, let me make sure I understand your requirements. You want to transfer all rows in Sheet1 that are "due" to Sheet2 (at the bottom of the data in Sheet2), is that correct? If yes, can you answer the following.

How do you define "due" (is it rows with the due date = system date)? What column is the due date in?

Regards,
BarrieBarrie Davidson

Posted by amy on October 30, 2001 2:34 PM

yes i want to transfer all rows in Sheet 1 that are 'due' to sheet 2. this will be the only information (except for column titles) that will be on this sheet.

The due dates are randomn dates. they are not uniform in any way.

The due date is in column 8 of a 9 column sheet

thank you.
amy

Posted by Barrie Davidson on October 31, 2001 5:54 AM

Amy, if I understand your requirements, this macro will work for you.

Sub ExtractDueDates()
' Written by Barrie Davidson
Dim DueDate As Date

DueDate = CDate(Format(Now(), "mmm-d-yyyy"))
For Each cell In Range("H2", Range("H2").End(xlDown).Address)
If cell.Value = DueDate Then
cell.EntireRow.Copy Destination:=Sheets("Sheet2").Range(Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).Address)
Application.CutCopyMode = False
End If
Next cell

End Sub


Let me know if you have any problems.
BarrieBarrie Davidson

Posted by amy on October 31, 2001 12:54 PM

this doesn't do anything. could i provide you with some more information?
thanks,
amy

Posted by Barrie Davidson on October 31, 2001 1:45 PM

If you'd like, you can e-mail me the spreadsheet and I can have a look at it (won't be able to do this until Friday night at the earliest though). Hopefully I'll be able to get a better handle on your problem.

Regards,
BarrieBarrie Davidson