Collect used areas of multiple spreadsheets into one...
 

Let me try to explain my problem.

Basically I am trying to set up a petty cash system where each person
has his own sheet - that I then want to collect into one big Master
sheet.

I have 10 sheets in a workbook (lets call them cash1, cash2, cash3
etc).
They all have the same layout and formulas...

The areas where the people input the data that I want copied is ranging
from A9-I34.

I could do a simple =and refer to each cell in the different sheets.
But there might not be information in all the rows (A9 to I34) in all
of the indvidul sheets.

To avoid having a long list with empty rows I would like to only
collect the rows used in a specified area of each sheet and then put
them continously in my Master sheet - as a total resume of ALL the
expenses in on lone list.

I cant figure out how to do this without ending up with a lot of empty
rows in my master.

Please advice me how to solve this problem... It is driving me nuts -
and I will be so happy for any help you might offer.

Thank you,
MivPiv

Collect used areas of multiple spreadsheets into one...
 

Why not create it with the empty rows and then delete the empty rows

Dim rng as Range
On Error Resume Next
set rng = columns(1).specialcells(xlblanks)
On Error goto 0
if not rng is nothing then
rng.Entirerow.delete
End if

This uses column 1 (A) to determine if the row should be deleted. Adjust to
suit.

--
Regards,
Tom Ogilvy



"mivpiv" wrote:

Let me try to explain my problem.

Basically I am trying to set up a petty cash system where each person
has his own sheet - that I then want to collect into one big Master
sheet.

I have 10 sheets in a workbook (lets call them cash1, cash2, cash3
etc).
They all have the same layout and formulas...

The areas where the people input the data that I want copied is ranging
from A9-I34.

I could do a simple =and refer to each cell in the different sheets.
But there might not be information in all the rows (A9 to I34) in all
of the indvidul sheets.

To avoid having a long list with empty rows I would like to only
collect the rows used in a specified area of each sheet and then put
them continously in my Master sheet - as a total resume of ALL the
expenses in on lone list.

I cant figure out how to do this without ending up with a lot of empty
rows in my master.

Please advice me how to solve this problem... It is driving me nuts -
and I will be so happy for any help you might offer.

Thank you,
MivPiv

Collect used areas of multiple spreadsheets into one...
 

Super!!!
I actually just figured that out... Thanks so much!!!
Miv


Tom Ogilvy wrote:
Why not create it with the empty rows and then delete the empty rows

Dim rng as Range
On Error Resume Next
set rng = columns(1).specialcells(xlblanks)
On Error goto 0
if not rng is nothing then
rng.Entirerow.delete
End if

This uses column 1 (A) to determine if the row should be deleted. Adjust to
suit.

--
Regards,
Tom Ogilvy



"mivpiv" wrote:

Let me try to explain my problem.

Basically I am trying to set up a petty cash system where each person
has his own sheet - that I then want to collect into one big Master
sheet.

I have 10 sheets in a workbook (lets call them cash1, cash2, cash3
etc).
They all have the same layout and formulas...

The areas where the people input the data that I want copied is ranging
from A9-I34.

I could do a simple =and refer to each cell in the different sheets.
But there might not be information in all the rows (A9 to I34) in all
of the indvidul sheets.

To avoid having a long list with empty rows I would like to only
collect the rows used in a specified area of each sheet and then put
them continously in my Master sheet - as a total resume of ALL the
expenses in on lone list.

I cant figure out how to do this without ending up with a lot of empty
rows in my master.

Please advice me how to solve this problem... It is driving me nuts -
and I will be so happy for any help you might offer.

Thank you,
MivPiv