First off, Hello!

I am new to the group so please bear with my stupidity...

I have an Excel SS like this:

Brand Store Number Shipped/Receive Date Item 1 Item 2 Item 3
Alpha 5017 3/19/2007 X
Beta 5027 3/19/2007 X

Etc, etc. there are about 50 rows across.

I'd like to copy the 1st 3 columns to another sheet (which I can do
with a macro, I'd just like to do it as part of a formula)

Then I would like to do some form of if/then statement to copy only
the cells and headers that have data in them to the same new sheet.

For instance, in the above example, I don't want the blank fields to
show.

Any ideas?

If I am being unclear (God knows I hardly understood it and I wrote
it), please let me know.

Thanks in advance!

Grim

Grim: formulas won't copy cells, only a macro subroutine will do that. You
can reference different worksheets in a formula such as

On sheet2 cell A1
='sheet1'!B7


Refercing cells cannot remove empty rows.
The best way is with a macro subroutine.
"Grim" wrote:

First off, Hello!

I am new to the group so please bear with my stupidity...

I have an Excel SS like this:

Brand Store Number Shipped/Receive Date Item 1 Item 2 Item 3
Alpha 5017 3/19/2007 X
Beta 5027 3/19/2007 X

Etc, etc. there are about 50 rows across.

I'd like to copy the 1st 3 columns to another sheet (which I can do
with a macro, I'd just like to do it as part of a formula)

Then I would like to do some form of if/then statement to copy only
the cells and headers that have data in them to the same new sheet.

For instance, in the above example, I don't want the blank fields to
show.

Any ideas?

If I am being unclear (God knows I hardly understood it and I wrote
it), please let me know.

Thanks in advance!

Grim

Joel,

Thanks for the response.

I got the macro to copy from 1 sheet to another down, is there a macro
that will search a row and paste only headers w/data in them to
another sheet?

I tried doing this in acces... but it just laughed at me.

Thanks again.

Grim

you need to do something like this

Sub copycolumns()

Sheets("sheet1").Activate
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set HeaderRange = Range(Cells(1, 1), Cells(1, Lastcol))

ColumnCount = 1
For Each cell In HeaderRange

If Not IsEmpty(cell) Then

cell.EntireColumn.Copy _
Destination:=Sheets("sheet2").Columns(ColumnCount)


ColumnCount = ColumnCount + 1
End If

Next cell

End Sub

"Grim" wrote:

Joel,

Thanks for the response.

I got the macro to copy from 1 sheet to another down, is there a macro
that will search a row and paste only headers w/data in them to
another sheet?

I tried doing this in acces... but it just laughed at me.

Thanks again.

Grim

This will work

Sub copycolumns()

Sheets("sheet1").Activate
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set HeaderRange = Range(Cells(1, 1), Cells(1, Lastcol))

ColumnCount = 1
For Each cell In HeaderRange

If Not IsEmpty(cell) Then

cell.EntireColumn.Copy _
Destination:=Sheets("sheet2").Columns(ColumnCount)


ColumnCount = ColumnCount + 1
End If

Next cell

End Sub


"Grim" wrote:

Joel,

Thanks for the response.

I got the macro to copy from 1 sheet to another down, is there a macro
that will search a row and paste only headers w/data in them to
another sheet?

I tried doing this in acces... but it just laughed at me.

Thanks again.

Grim

excel macros

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com