copying data and skipping hidden lines

mozzer13 · May 10th, 2002, 01:39 PM

I work in a call center, and I do daily reports in Excel on call information for our customer service reps. I have a program called HiPath Composer which generates this data, and my Excel workbook covers each day for a month and does a lot of calculating at the end of the month. Because of my need for totalling at the end, each CSR must remain on the same line on each day. As not everyone works every day, I hide a person's line if they did not work on a particular day and no data was generated for them.

So here's the problem: This is a huge amount of typing every day and I want to automate the process. Composer will export into an Excel file. However, when I try to copy and paste from the exported file to that day's spreadsheet, I am unable to because some lines in the Excel document are hidden and Excel wants to automatically fill those in. What I need to happen when I copy is for Excel to skip lines that are hidden and only put data into the lines that are showing. I've attempted to set up filters for this and such, but unless I copy line by line (which takes nearly the time of typing the data myself), it will not ignore the hidden lines. Is there an Excel function that will cause the hidden lines to be ignored when copying data or will I need some sort of VB application for this? If necessary, I can send you an example of my Excel workbook and the exported Composer file. HELP!!!

[ This Message was edited by: mozzer13 on 2002-05-10 12:40 ]

cpod · May 10th, 2002, 02:45 PM

If I understand you correctly, you just want to copy data without the hidden rows and columns?

Just select the range of data to copy and then:

Edit/go to/special/visible cells only/ok

Now only the visible cells are selected and you can copy and paste.

mozzer13 · May 13th, 2002, 10:23 AM

Actually, I want to copy data into a spreadsheet that has hidden lines. I want my copied data to skip the hidden lines and only fill in lines that are visible. I tried using the function you suggested, but it isn't working. Thanks for giving it a try!

Chris Davison · May 13th, 2002, 11:23 AM

MOzzer,

I don't suppose your hidden rows have any uniformity to them do they ?

I'm guessing they're basically random....

Any number-sequence ? :

row 2,3,4,6,7,8,10,11,12 etc hopefully ?

Chris

mozzer13 · May 13th, 2002, 11:46 AM

They are random, just whomever isn't working that day. No data will come up for them on my reports if they didn't log into the phones, so I hide their line on the spreadsheet for that day.

Mark W. · May 13th, 2002, 11:50 AM

The Paste area must be contiguous.

mozzer13 · May 13th, 2002, 11:52 AM

Could a Visual Basic app be added to make it skip hidden lines when I paste in copied data?

Mark W. · May 13th, 2002, 12:56 PM

Quote:

On 2002-05-13 10:52, mozzer13 wrote:
Could a Visual Basic app be added to make it skip hidden lines when I paste in copied data?

VBA's Paste method would have the same restriction. I suppose someone could write a VBA to do a row-by-row copy and paste, but that would be very inefficient. Perhaps you should re-think your procedures. Why are are you hiding rows?

Nimrod · May 13th, 2002, 01:01 PM

In the text you want to import:
- is the order always the same ? i.e. John's entry first , then Tom then Dick
- If Tom didn't work Row 1 would be john,row 2 Dick

mozzer13 · May 13th, 2002, 01:09 PM

Mark: I hide rows I don't need that day in order to keep each person on the same row every day. At the end of the month, all of their phone statistics are totalled for me, and if they are in a different place every day, those totals come out wrong.

Nimrod: It is the same order every day. For example, Nigel is line 1 and Tony is line 2. If Nigel did not work on Monday, he remains line 1, I just hide that line for that day so that I know to put Tony's stats in first that day and to leave Nigel blank. The imported text is always the same order, only some people don't show up on various days because they aren't working.

[ This Message was edited by: mozzer13 on 2002-05-13 12:11 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 10th, 2002, 01:39 PM	#1
mozzer13 New Member Join Date: May 2002 Location: Christopher Posts: 5	I work in a call center, and I do daily reports in Excel on call information for our customer service reps. I have a program called HiPath Composer which generates this data, and my Excel workbook covers each day for a month and does a lot of calculating at the end of the month. Because of my need for totalling at the end, each CSR must remain on the same line on each day. As not everyone works every day, I hide a person's line if they did not work on a particular day and no data was generated for them. So here's the problem: This is a huge amount of typing every day and I want to automate the process. Composer will export into an Excel file. However, when I try to copy and paste from the exported file to that day's spreadsheet, I am unable to because some lines in the Excel document are hidden and Excel wants to automatically fill those in. What I need to happen when I copy is for Excel to skip lines that are hidden and only put data into the lines that are showing. I've attempted to set up filters for this and such, but unless I copy line by line (which takes nearly the time of typing the data myself), it will not ignore the hidden lines. Is there an Excel function that will cause the hidden lines to be ignored when copying data or will I need some sort of VB application for this? If necessary, I can send you an example of my Excel workbook and the exported Composer file. HELP!!! [ This Message was edited by: mozzer13 on 2002-05-10 12:40 ]

May 10th, 2002, 02:45 PM	#2
cpod Board Regular Join Date: Apr 2002 Posts: 124	If I understand you correctly, you just want to copy data without the hidden rows and columns? Just select the range of data to copy and then: Edit/go to/special/visible cells only/ok Now only the visible cells are selected and you can copy and paste.

May 13th, 2002, 10:23 AM	#3
mozzer13 New Member Join Date: May 2002 Location: Christopher Posts: 5	Actually, I want to copy data into a spreadsheet that has hidden lines. I want my copied data to skip the hidden lines and only fill in lines that are visible. I tried using the function you suggested, but it isn't working. Thanks for giving it a try!

May 13th, 2002, 11:23 AM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	MOzzer, I don't suppose your hidden rows have any uniformity to them do they ? I'm guessing they're basically random.... Any number-sequence ? : row 2,3,4,6,7,8,10,11,12 etc hopefully ? Chris

May 13th, 2002, 11:46 AM	#5
mozzer13 New Member Join Date: May 2002 Location: Christopher Posts: 5	They are random, just whomever isn't working that day. No data will come up for them on my reports if they didn't log into the phones, so I hide their line on the spreadsheet for that day.

May 13th, 2002, 11:50 AM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	The Paste area must be contiguous.

May 13th, 2002, 11:52 AM	#7
mozzer13 New Member Join Date: May 2002 Location: Christopher Posts: 5	Could a Visual Basic app be added to make it skip hidden lines when I paste in copied data?

May 13th, 2002, 01:01 PM	#9
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	In the text you want to import: - is the order always the same ? i.e. John's entry first , then Tom then Dick - If Tom didn't work Row 1 would be john,row 2 Dick

May 13th, 2002, 01:09 PM	#10
mozzer13 New Member Join Date: May 2002 Location: Christopher Posts: 5	Mark: I hide rows I don't need that day in order to keep each person on the same row every day. At the end of the month, all of their phone statistics are totalled for me, and if they are in a different place every day, those totals come out wrong. Nimrod: It is the same order every day. For example, Nigel is line 1 and Tony is line 2. If Nigel did not work on Monday, he remains line 1, I just hide that line for that day so that I know to put Tony's stats in first that day and to leave Nigel blank. The imported text is always the same order, only some people don't show up on various days because they aren't working. [ This Message was edited by: mozzer13 on 2002-05-13 12:11 ]