How to write VBA code for Excel that copies rows to a new worksheet if it fulfills requirements?

[kflare]

So I have multiple sheets that have columns labeled "Course" "Name" and "Date Certified." I'm trying to create a list on a new sheet that compiles all rows from each of the other sheets if there is a date in the date certified column. I've tried fooling around with IF statements and making a Macro but the best I've come up with just moves the "Course" cell in that row if there is a date enters (I need the whole row information) and when it moves it over it leaves blanks between moved cells because the matching cell on the last worksheet did not have a date. I'm trying to make a macro so that a list is created every time I open the document it updates the list. I took a class years ago so I remember the basics but I'm super rusty on actually writing codes. If anyone has suggestions on what do to that would be great thanks!

 

[hiker95]

kflare,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this

 

[kflare]

Hello again,
I'm using windows7 2010 This is the code I have so far but I keep getting an error message of subscript our of range.
Private Sub Workbook_Open()

Dim SheetNumber As Integer
Dim SheetName As String
Dim SingleCell As Range
Dim ListRange As Range

Sheets("WebPDF").Select
If Range("A2").Value = "" Then
Range("A2").Select
Else
Range("A1").End(xlDown).Select
End If

For SheetNumber = 2006 To 2011

SheetName = CStr(SheetNumber)
Worksheets(SheetName).Select
Set ListRange = Range("A1", Range("A1").End(xlDown))

Sheets("WebPDF").Select

For Each SingleCell In ListRange
If IsDate(SingleCell.Offset(0, 11).Value) Then
SingleCell.EntireRow.Copy
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
SingleCell.EntireRow.Delete xlShiftUp
End If
Next SingleCell

Next SheetNumber

End Sub

I can't manage to get a screenshot together and I can't upload the document because it has client information on it.
There are titles for the columns in row 1 columns go from A-P.
Hope this helps more for someone to help me.
Thanks!

 

[hiker95]

kflare,

I can't manage to get a screenshot together and I can't upload the document because it has client information on it.
There are titles for the columns in row 1 columns go from A-P.

You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:


If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.


If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this