
Originally Posted by
union865
you wouldn't want to print it out looking like that.
So how would you want it looking!?

Originally Posted by
union865
a function or formula set that will look at cell a1 and if it contains any text to copy it into cell g1.
OK. So that deals with cell A1, what about the rest?
The following macro will copy values from a block (as defined in the first line) in the active sheet to a new sheet as values. On that new sheet, it will remove blanks.
I can only guess what you want so it's probably wrong. (I notice that the ranges you quote are in pairs, so maybe you want the results in just two columns? I really don't know as I can neither look over your shoulder to see what's on your screen nor read your mind.)
Code:
Sub Macro7()
SourceRangeAddress = "A1:D9" '<< adjust this.
Set oldSht = ActiveSheet
Set NewSht = Sheets.Add
NewSht.Range("A1").Resize(Range(SourceRangeAddress).Rows.Count, Range(SourceRangeAddress).Columns.Count).Value = oldSht.Range(SourceRangeAddress).Value
NewSht.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub
Bookmarks