Problem copying data between spreadsheets

[PHalf]

Hi

I have a spreadsheet that contains a lot of information. What I regularly do is run reports off of it by running a macro that hides certain columns and then copy and paste the relevant rows into another spreadsheet. This has been working for quite a while now, but yesterday some problems started. After I hide the columns, I copy about half a dozen rows (all of the columns, there's now only 5 after hiding) and paste them into a spreadsheet set up for the report. Now, the information that is pasted includes data from some of the hidden columns and thus doesn't fit the layout properly. I've checked the clipboard to see what is being copied and it's only the cells that I've selected, so when I paste it's pulling out extra information to what has been copied.

Thanks in advance for any help or advice,

PHalf

 

[DaleWatson123321]

PHalf,

Not knowing the verion of 123 makes it a bit more difficult to pin down the problem/solution. However, this is what I'd suggest...

1) Use the Help function and search for something like "hidden columns", and look for suppression of copying data from the hidden columns.

2) If it turns out there is no "setting" for preventing the copying of data from hidden columns, then I'd recommend you utilize 123's "very" powerful capability to manipulate data using its "database functionality". (Or, perhaps after reading the following, you might decide to utilize the power of 123 as described below)

2a) This refers to setting up your "source" data sheet as a (spreadsheet-based) "database" - i.e. with field names for each column.

2b) Set up your "criteria" on a separate sheet. This "criteria" needs to include one or more of the field names on one row, and below each field name, enter the criteria for the specific data you need to extract.

2c) On your "destination" sheet (the one you currently use), include the field names for the fields you want included.

This of course means you don't have to bother hiding those fields you don't want included.

Please also appreciate that 123 permits you to change the order of the field names in your "destination" sheet.

Because I use release 5 (and not too frequently now), I'm not up-to-date on the latest macro code for extracting the data. However, here's "old" code that might still work...

/DQIdata~Odest~Ccrit~EQ

...where "data" refers to a range name assigned to your source data - with the top row of this range being the row containing your field names. This range name can extend "beyond" the current range occupied with data - in regard to the ROWS included. Restrict the COLUMNS to the columns containing your data - i.e. the ones with field names.

...where "dest" refers to a range name assigned to the row on your destination sheet containing your field names.

(By referencing just the top row contain the field names, this will cause ALL data immediately below to be deleted when it's replaced with the new data.)

...where "crit" refers to a range name assigned to the criteria range previously mentioned.

123 is particularly "powerful" in terms of being able to write easy to "very" complex formulas as part of the criteria - if required - to enable the user to be very precise in extracting exactly the data you require for any given report.

Please appreciate that regarding the range names, you naturally can use whatever range names you want - instead of the names I happen to have used in this example.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca