can i copy data without row numbers?

[Guest]

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

 

[Bernard Liengme]

Rewrite the macro?
If you tell us the code someone may point the way.
best wishes

 

[Guest]

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

 

[Jim May]

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

 

[Jim May]

In (new) A1 enter

better make that

In A2 enter

 

[Guest]

Thanks Jim gonna try it now

Dave

In (new) A1 enter

better make that

In A2 enter

 

[Guest]

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]

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 (e-mail address removed)

 

[JMay]

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

 

[Guest]

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

Dave

 

[Guest]

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.

 

[Gord Dibben]

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 F5>Special>Visible cells only.


Gord Dibben Excel MVP

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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.

 

[Guest]

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