can i copy data without row numbers?
 

i have 600 potential payees in an excel spreadsheet. Of these only 20%
receive payment in any one week. By using macros I can produce a worksheet
showing all payees paid in any one week, hiding all others. However the
resulting spreadsheet still retains the original row numbers as the zero
value rows are only hidden. Is there any way that I can have the spreadsheet
ignore the original row references so that the first payee is shown in row 1,
the seconf in row 2 etc?Thanks

Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only 20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others. However the
resulting spreadsheet still retains the original row numbers as the zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is shown in row
1,
the seconf in row 2 etc?Thanks

Hi Bernard

Thanks for answering.

The code simply goes through each row and hides the rows where the value in
column D is zero.

As a result I get a nice tidy sheet with only the positive values showing.
In turn this is transferred to the bank's system so tht I can make the
payments. The zero value rows are only hidden. I want to produce another
sheet where only the positive values appear so as to avoid the banks system
havving to read 600 entries, most of which are zero vales.

In the ideal world the value of (say) row17 would appear on row 1 of the new
sheet etc. However next week it might be that row 12 is the first payee etc.
Simply I want to take (say) 100 rows from sheet 1, regardless of row number
and transfer them to the first hundred rows on sheet 2.

Thanks

Dave.

ps: I tried to run the macro with "delete" instead of hide but it got stuck
once it eliminated the first row.

The first

"Bernard Liengme" wrote:

Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only 20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others. However the
resulting spreadsheet still retains the original row numbers as the zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is shown in row
1,
the seconf in row 2 etc?Thanks

Insert a new Column A (give it the headername NewRow#
In (new) A1 enter =subtotal(3,$A$1:A1)
copy down from A2:A600

Engage the Auto-Filter on your table of data.

Now
Select from the appropriate FieldHeader dropdown the current weeks checks
Only your Current weeks check should be visible.
Check out the content of Column A (now) !!
Does it Look like you want it to?
HTH



"Bernard Liengme" wrote in message
...
Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only 20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others. However the
resulting spreadsheet still retains the original row numbers as the zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is shown in
row
1,
the seconf in row 2 etc?Thanks

In (new) A1 enter

better make that

In A2 enter



"Jim May" wrote in message
news:aa3Sd.52466$EG1.6656@lakeread04...
Insert a new Column A (give it the headername NewRow#
In (new) A1 enter =subtotal(3,$A$1:A1)
copy down from A2:A600

Engage the Auto-Filter on your table of data.

Now
Select from the appropriate FieldHeader dropdown the current weeks checks
Only your Current weeks check should be visible.
Check out the content of Column A (now) !!
Does it Look like you want it to?
HTH



"Bernard Liengme" wrote in message
...
Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only 20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others. However
the
resulting spreadsheet still retains the original row numbers as the
zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is shown in
row
1,
the seconf in row 2 etc?Thanks

Thanks Jim gonna try it now

Dave

"Jim May" wrote:

In (new) A1 enter

better make that

In A2 enter



"Jim May" wrote in message
news:aa3Sd.52466$EG1.6656@lakeread04...
Insert a new Column A (give it the headername NewRow#
In (new) A1 enter =subtotal(3,$A$1:A1)
copy down from A2:A600

Engage the Auto-Filter on your table of data.

Now
Select from the appropriate FieldHeader dropdown the current weeks checks
Only your Current weeks check should be visible.
Check out the content of Column A (now) !!
Does it Look like you want it to?
HTH



"Bernard Liengme" wrote in message
...
Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only 20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others. However
the
resulting spreadsheet still retains the original row numbers as the
zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is shown in
row
1,
the seconf in row 2 etc?Thanks

Hi Jim,

It works and gives me the same result as the macro but much more
effectively. However I am still stuck with the same row numbers. For example
my first payee this week is in row 17; the next in row 43 and so on.
Ultimately I have to send a file to the bank where row 17 on the old sheet is
on row 1 of a new sheet, 43 of the old on 2 of the new etc.. Thanks again

Dave

"Jim May" wrote:

In (new) A1 enter

better make that

In A2 enter



"Jim May" wrote in message
news:aa3Sd.52466$EG1.6656@lakeread04...
Insert a new Column A (give it the headername NewRow#
In (new) A1 enter =subtotal(3,$A$1:A1)
copy down from A2:A600

Engage the Auto-Filter on your table of data.

Now
Select from the appropriate FieldHeader dropdown the current weeks checks
Only your Current weeks check should be visible.
Check out the content of Column A (now) !!
Does it Look like you want it to?
HTH



"Bernard Liengme" wrote in message
...
Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only 20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others. However
the
resulting spreadsheet still retains the original row numbers as the
zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is shown in
row
1,
the seconf in row 2 etc?Thanks

I'm a bit confused with what you mean.
If you'll send me the file I'll take a look at it.

Send to


"dave glynn" wrote in message
...
Hi Jim,

It works and gives me the same result as the macro but much more
effectively. However I am still stuck with the same row numbers. For
example
my first payee this week is in row 17; the next in row 43 and so on.
Ultimately I have to send a file to the bank where row 17 on the old sheet
is
on row 1 of a new sheet, 43 of the old on 2 of the new etc.. Thanks again

Dave

"Jim May" wrote:

In (new) A1 enter

better make that

In A2 enter



"Jim May" wrote in message
news:aa3Sd.52466$EG1.6656@lakeread04...
Insert a new Column A (give it the headername NewRow#
In (new) A1 enter =subtotal(3,$A$1:A1)
copy down from A2:A600

Engage the Auto-Filter on your table of data.

Now
Select from the appropriate FieldHeader dropdown the current weeks
checks
Only your Current weeks check should be visible.
Check out the content of Column A (now) !!
Does it Look like you want it to?
HTH



"Bernard Liengme" wrote in message
...
Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only
20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others.
However
the
resulting spreadsheet still retains the original row numbers as
the
zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is
shown in
row
1,
the seconf in row 2 etc?Thanks

Just curious, but check your File, PageSetup, Sheet, Print Section for Row
and Column Headings..
Is it by chance checked? If so, Uncheck it..
If it was - Does that help?
Jim

"dave glynn" wrote in message
...
Hi Jim,

It works and gives me the same result as the macro but much more
effectively. However I am still stuck with the same row numbers. For
example
my first payee this week is in row 17; the next in row 43 and so on.
Ultimately I have to send a file to the bank where row 17 on the old sheet
is
on row 1 of a new sheet, 43 of the old on 2 of the new etc.. Thanks again

Dave

"Jim May" wrote:

In (new) A1 enter

better make that

In A2 enter



"Jim May" wrote in message
news:aa3Sd.52466$EG1.6656@lakeread04...
Insert a new Column A (give it the headername NewRow#
In (new) A1 enter =subtotal(3,$A$1:A1)
copy down from A2:A600

Engage the Auto-Filter on your table of data.

Now
Select from the appropriate FieldHeader dropdown the current weeks
checks
Only your Current weeks check should be visible.
Check out the content of Column A (now) !!
Does it Look like you want it to?
HTH



"Bernard Liengme" wrote in message
...
Rewrite the macro?
If you tell us the code someone may point the way.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dave glynn" wrote in message
...
i have 600 potential payees in an excel spreadsheet. Of these only
20%
receive payment in any one week. By using macros I can produce a
worksheet
showing all payees paid in any one week, hiding all others.
However
the
resulting spreadsheet still retains the original row numbers as
the
zero
value rows are only hidden. Is there any way that I can have the
spreadsheet
ignore the original row references so that the first payee is
shown in
row
1,
the seconf in row 2 etc?Thanks

It was unchecked, Jim, so doesn't do anything. Have e-mailed the file to you.
Once again, thanks

Dave

"JMay" wrote:

Just curious, but check your File, PageSetup, Sheet, Print Section for Row
and Column Headings..
Is it by chance checked? If so, Uncheck it..
If it was - Does that help?
Jim

Hi, Dave;
How about copying the cells in the filtered sheet, and Paste Special into a
new sheet, with "Skip blanks" checked in the Paste Special dialog? Send the
new sheet.
Wouldn't that do it?
Regards,
Ian.

Ian

I think you misunderstand the purpose of "skip blanks".

Skip Blanks option will still copy the blanks, the blanks just won't overwrite
existing data if copied onto a range containing data.

See help on "skip blanks".

I think what you were looking for was F5SpecialVisible cells only.


Gord Dibben Excel MVP

On Sun, 20 Feb 2005 13:37:02 -0800, IanRoy
wrote:

Hi, Dave;
How about copying the cells in the filtered sheet, and Paste Special into a
new sheet, with "Skip blanks" checked in the Paste Special dialog? Send the
new sheet.
Wouldn't that do it?
Regards,
Ian.

Thanks ian, it works!!! Thanks to everyone else too

"IanRoy" wrote:

Hi, Dave;
How about copying the cells in the filtered sheet, and Paste Special into a
new sheet, with "Skip blanks" checked in the Paste Special dialog? Send the
new sheet.
Wouldn't that do it?
Regards,
Ian.

Hi, dave;
Glad it worked for you.
Ian.

Thanks ian, it works!!! Thanks to everyone else too

"IanRoy" wrote:

Hi, Dave;
How about copying the cells in the filtered sheet, and Paste Special into a
new sheet, with "Skip blanks" checked in the Paste Special dialog? Send the
new sheet.
Wouldn't that do it?
Regards,
Ian.

Hi, Gord;

Hmm, when I tested it:
Sheet1, A1 contained "x"
Sheet1, A2 was blank
Sheet1, A3 contained "y"
I filtered column A for NonBlanks and copied A1:A3
Paste special - skip blanks into Sheet2, A1. Results:
Sheet2, A1 contained "x"
Sheet2, A2 contained "y"
Where did the copy of Sheet1, A2 go?

I didn't understand the "visible cells only" method.
I just tried it in place of paste special, and it gave me this alert:
"! The selection already contains visible cells."

Excel 2003
Regards,
Ian.

"Gord Dibben" wrote:

Ian

I think you misunderstand the purpose of "skip blanks".

Skip Blanks option will still copy the blanks, the blanks just won't overwrite
existing data if copied onto a range containing data.

See help on "skip blanks".

I think what you were looking for was F5SpecialVisible cells only.


Gord Dibben Excel MVP

Hi Gord,
Maybe I do. I just tried it without the "skip blanks" part, and it still
worked. Just paste,
<embarrassed
Ian.

"Gord Dibben" wrote:

Ian

I think you misunderstand the purpose of "skip blanks".