Macro to Copy & Paste only rows with data

[KWorsham]

I have the following issue:
Rows are populated with data based on date listed (Rows 6-20). The formula puts a " " if the date cell is blank since the range of data is not the same each time the data pull is imported. What I need is a macro that only copies and then pastes to another sheet ("ARCHIVE") the rows that have data; could be rows 6-20, 6-15 or just row 6.

Thank you in advance for any time and assistance given to my little issue.

 

[VoG]

Which column has the " "?

 

[KWorsham]

In the example, rows 6-10 have data and 11-20 have " ". But next time the data is pulled, it could be that rows 6-10 or even just row 6 that could have data and the remaining rows through 20 would have the " ".

 

[VoG]

Yes, but which column?

 

[KWorsham]

Sorry...I'm a dork... read "column" but heard "row" in my head.
Columns B:ANU but the headers are already on the "ARCHIVE" tab so would only need the data copied over.

 

[VoG]

Sorry but I am still not understanding. You want to copy rows 6:20 where something doesn't contain " ". What is that something?

 

[KWorsham]

Rows 6-20 could contain data or " ". What I would like a macro to do is to copy and paste into the "ARCHIVE" sheet but each time, I need it to only copy the rows that do have data. Would it be easier if the rows that had didn't have data but had " " were a certain fill color and then I could have the macro copy and paste only the rows with that color?

 

[VoG]

Assuming column B contains the " "

Sub test()
Dim i As Integer
For i = 6 To 20
    If Range("B" & i) <> " " Then Rows(i).Copy Destination:=Sheets("ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

 

[KWorsham]

Perfect. Thank you!

 

[p.huddy]

Just reading through this marco code as looking to use it myself.

How would I change it to copy into another Workbook rather than another sheet?

Thanks!