User form to copy columns of data & Paste As Values into another worksheet

[AusSteelMan]

Hi there everyone.

I have a worksheet with many columns of information for a record that serve several purposes. I currently copy some of the columns I need and Paste Special...Values into a new worksheet and then some different columns into another new sheet. Each workbook generated will need this process carried out several times. Note: the extracted data is used as a "load sheet" for uploading in to our SAP Master Data, so the columns and format (text not formula) is quite important. This is of course becoming arduous and prime for some code.

Here is what I think I would like (built on my still reasonably limited VBA ability).

I would like to have a macro that invokes a userform (or similar) to ask (radio button?) which column set i would like:
1. SAP FLOC Upload columns
2. Instrument label detail columns
3. Other individually selected columns. If the 3rd option is selected, then another userform that lists all the column labels which can be then selected (tickbox style?)

The macro would then copy the desired columns and Paste Special...Values and Column Widths into a new worksheet. This worksheet would be placed at the End (behind all the existing visible worksheets) and the user would be prompted to give the worksheet a name.

I have used looked at some old/existing code as well as record a new macro for the (easy) part of Copy>PasteSpecial...Values into a new sheet, but don't think I can pull off the whole deal myself...hence asking for some help

I have generated the following example book to show what the raw data and new worksheet would look like.

Raw Data:
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 191px"><COL style="WIDTH: 193px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Column Label 1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Column Label 2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col Label 10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>COL 1, ROW 3 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 3 text - with Cond format</TD><TD>C3, R 3 text</TD><TD>C4, R 3 text</TD><TD>C5, R 3 text</TD><TD>C6, R 3 text</TD><TD>C7, R 3 text</TD><TD>C8, R 3 text</TD><TD>C9, R 3 text</TD><TD>C10, R 3 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>COL 1, ROW 4 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 4 text - with Cond format</TD><TD>C3, R 4 text</TD><TD>C4, R 4 text</TD><TD>C5, R 4 text</TD><TD>C6, R 4 text</TD><TD>C7, R 4 text</TD><TD>C8, R 4 text</TD><TD>C9, R 4 text</TD><TD>C10, R 4 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>COL 1, ROW 5 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 5 text - with Cond format</TD><TD>C3, R 5 text</TD><TD>C4, R 5 text</TD><TD>C5, R 5 text</TD><TD>C6, R 5 text</TD><TD>C7, R 5 text</TD><TD>C8, R 5 text</TD><TD>C9, R 5 text</TD><TD>C10, R 5 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>COL 1, ROW 6 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 6 text - with Cond format</TD><TD>C3, R 6 text</TD><TD>C4, R 6 text</TD><TD>C5, R 6 text</TD><TD>C6, R 6 text</TD><TD>C7, R 6 text</TD><TD>C8, R 6 text</TD><TD>C9, R 6 text</TD><TD>C10, R 6 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>COL 1, ROW 7 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 7 text - with Cond format</TD><TD>C3, R 7 text</TD><TD>C4, R 7 text</TD><TD>C5, R 7 text</TD><TD>C6, R 7 text</TD><TD>C7, R 7 text</TD><TD>C8, R 7 text</TD><TD>C9, R 7 text</TD><TD>C10, R 7 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>COL 1, ROW 8 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 8 text - with Cond format</TD><TD>C3, R 8 text</TD><TD>C4, R 8 text</TD><TD>C5, R 8 text</TD><TD>C6, R 8 text</TD><TD>C7, R 8 text</TD><TD>C8, R 8 text</TD><TD>C9, R 8 text</TD><TD>C10, R 8 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>COL 1, ROW 9 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 9 text - with Cond format</TD><TD>C3, R 9 text</TD><TD>C4, R 9 text</TD><TD>C5, R 9 text</TD><TD>C6, R 9 text</TD><TD>C7, R 9 text</TD><TD>C8, R 9 text</TD><TD>C9, R 9 text</TD><TD>C10, R 9 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>COL 1, ROW 10 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 10 text - with Cond format</TD><TD>C3, R 10 text</TD><TD>C4, R 10 text</TD><TD>C5, R 10 text</TD><TD>C6, R 10 text</TD><TD>C7, R 10 text</TD><TD>C8, R 10 text</TD><TD>C9, R 10 text</TD><TD>C10, R 10 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>COL 1, ROW 11 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 11 text - with Cond format</TD><TD>C3, R 11 text</TD><TD>C4, R 11 text</TD><TD>C5, R 11 text</TD><TD>C6, R 11 text</TD><TD>C7, R 11 text</TD><TD>C8, R 11 text</TD><TD>C9, R 11 text</TD><TD>C10, R 11 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>COL 1, ROW 12 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 12 text - with Cond format</TD><TD>C3, R 12 text</TD><TD>C4, R 12 text</TD><TD>C5, R 12 text</TD><TD>C6, R 12 text</TD><TD>C7, R 12 text</TD><TD>C8, R 12 text</TD><TD>C9, R 12 text</TD><TD>C10, R 12 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>COL 1, ROW 13 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 13 text - with Cond format</TD><TD>C3, R 13 text</TD><TD>C4, R 13 text</TD><TD>C5, R 13 text</TD><TD>C6, R 13 text</TD><TD>C7, R 13 text</TD><TD>C8, R 13 text</TD><TD>C9, R 13 text</TD><TD>C10, R 13 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>COL 1, ROW 14 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 14 text - with Cond format</TD><TD>C3, R 14 text</TD><TD>C4, R 14 text</TD><TD>C5, R 14 text</TD><TD>C6, R 14 text</TD><TD>C7, R 14 text</TD><TD>C8, R 14 text</TD><TD>C9, R 14 text</TD><TD>C10, R 14 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>COL 1, ROW 15 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 15 text - with Cond format</TD><TD>C3, R 15 text</TD><TD>C4, R 15 text</TD><TD>C5, R 15 text</TD><TD>C6, R 15 text</TD><TD>C7, R 15 text</TD><TD>C8, R 15 text</TD><TD>C9, R 15 text</TD><TD>C10, R 15 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>COL 1, ROW 16 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 16 text - with Cond format</TD><TD>C3, R 16 text</TD><TD>C4, R 16 text</TD><TD>C5, R 16 text</TD><TD>C6, R 16 text</TD><TD>C7, R 16 text</TD><TD>C8, R 16 text</TD><TD>C9, R 16 text</TD><TD>C10, R 16 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>COL 1, ROW 17 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 17 text - with Cond format</TD><TD>C3, R 17 text</TD><TD>C4, R 17 text</TD><TD>C5, R 17 text</TD><TD>C6, R 17 text</TD><TD>C7, R 17 text</TD><TD>C8, R 17 text</TD><TD>C9, R 17 text</TD><TD>C10, R 17 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>COL 1, ROW 18 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 18 text - with Cond format</TD><TD>C3, R 18 text</TD><TD>C4, R 18 text</TD><TD>C5, R 18 text</TD><TD>C6, R 18 text</TD><TD>C7, R 18 text</TD><TD>C8, R 18 text</TD><TD>C9, R 18 text</TD><TD>C10, R 18 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>COL 1, ROW 19 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 19 text - with Cond format</TD><TD>C3, R 19 text</TD><TD>C4, R 19 text</TD><TD>C5, R 19 text</TD><TD>C6, R 19 text</TD><TD>C7, R 19 text</TD><TD>C8, R 19 text</TD><TD>C9, R 19 text</TD><TD>C10, R 19 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>COL 1, ROW 20 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 20 text - with Cond format</TD><TD>C3, R 20 text</TD><TD>C4, R 20 text</TD><TD>C5, R 20 text</TD><TD>C6, R 20 text</TD><TD>C7, R 20 text</TD><TD>C8, R 20 text</TD><TD>C9, R 20 text</TD><TD>C10, R 20 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>COL 1, ROW 21 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 21 text - with Cond format</TD><TD>C3, R 21 text</TD><TD>C4, R 21 text</TD><TD>C5, R 21 text</TD><TD>C6, R 21 text</TD><TD>C7, R 21 text</TD><TD>C8, R 21 text</TD><TD>C9, R 21 text</TD><TD>C10, R 21 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>COL 1, ROW 22 text - formula derived</TD><TD style="BACKGROUND-COLOR: #ccffcc">COL 2, Row 22 text - with Cond format</TD><TD>C3, R 22 text</TD><TD>C4, R 22 text</TD><TD>C5, R 22 text</TD><TD>C6, R 22 text</TD><TD>C7, R 22 text</TD><TD>C8, R 22 text</TD><TD>C9, R 22 text</TD><TD>C10, R 22 text</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A3</TD><TD>="COL "&COLUMN()&", ROW "&ROW()&" text - formula derived"</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #ff0000; BORDER-BOTTOM-COLOR: #ff0000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #ff0000; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #ff0000; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Conditional formatting </TD></TR><TR><TD><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=0 border=1><TBODY><TR><TD>Cell</TD><TD>Nr.: / Condition</TD><TD>Format</TD></TR><TR><TD>B3</TD><TD>1. / Formula is =IF(LEFT(B3,3)="COL",TRUE,FALSE)</TD><TD style="BACKGROUND-COLOR: #ccffcc">Abc</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

New worksheet:
Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 191px"><COL style="WIDTH: 193px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Column Label 1</TD><TD>Column Label 2</TD><TD>Col Label 4</TD><TD>Col Label 6</TD><TD>Col Label 8</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>COL 1, ROW 3 text - formula derived</TD><TD>COL 2, Row 3 text - with Cond format</TD><TD>C4, R 3 text</TD><TD>C6, R 3 text</TD><TD>C8, R 3 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>COL 1, ROW 4 text - formula derived</TD><TD>COL 2, Row 4 text - with Cond format</TD><TD>C4, R 4 text</TD><TD>C6, R 4 text</TD><TD>C8, R 4 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>COL 1, ROW 5 text - formula derived</TD><TD>COL 2, Row 5 text - with Cond format</TD><TD>C4, R 5 text</TD><TD>C6, R 5 text</TD><TD>C8, R 5 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>COL 1, ROW 6 text - formula derived</TD><TD>COL 2, Row 6 text - with Cond format</TD><TD>C4, R 6 text</TD><TD>C6, R 6 text</TD><TD>C8, R 6 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>COL 1, ROW 7 text - formula derived</TD><TD>COL 2, Row 7 text - with Cond format</TD><TD>C4, R 7 text</TD><TD>C6, R 7 text</TD><TD>C8, R 7 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>COL 1, ROW 8 text - formula derived</TD><TD>COL 2, Row 8 text - with Cond format</TD><TD>C4, R 8 text</TD><TD>C6, R 8 text</TD><TD>C8, R 8 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>COL 1, ROW 9 text - formula derived</TD><TD>COL 2, Row 9 text - with Cond format</TD><TD>C4, R 9 text</TD><TD>C6, R 9 text</TD><TD>C8, R 9 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>COL 1, ROW 10 text - formula derived</TD><TD>COL 2, Row 10 text - with Cond format</TD><TD>C4, R 10 text</TD><TD>C6, R 10 text</TD><TD>C8, R 10 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>COL 1, ROW 11 text - formula derived</TD><TD>COL 2, Row 11 text - with Cond format</TD><TD>C4, R 11 text</TD><TD>C6, R 11 text</TD><TD>C8, R 11 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>COL 1, ROW 12 text - formula derived</TD><TD>COL 2, Row 12 text - with Cond format</TD><TD>C4, R 12 text</TD><TD>C6, R 12 text</TD><TD>C8, R 12 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>COL 1, ROW 13 text - formula derived</TD><TD>COL 2, Row 13 text - with Cond format</TD><TD>C4, R 13 text</TD><TD>C6, R 13 text</TD><TD>C8, R 13 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>COL 1, ROW 14 text - formula derived</TD><TD>COL 2, Row 14 text - with Cond format</TD><TD>C4, R 14 text</TD><TD>C6, R 14 text</TD><TD>C8, R 14 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>COL 1, ROW 15 text - formula derived</TD><TD>COL 2, Row 15 text - with Cond format</TD><TD>C4, R 15 text</TD><TD>C6, R 15 text</TD><TD>C8, R 15 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>COL 1, ROW 16 text - formula derived</TD><TD>COL 2, Row 16 text - with Cond format</TD><TD>C4, R 16 text</TD><TD>C6, R 16 text</TD><TD>C8, R 16 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>COL 1, ROW 17 text - formula derived</TD><TD>COL 2, Row 17 text - with Cond format</TD><TD>C4, R 17 text</TD><TD>C6, R 17 text</TD><TD>C8, R 17 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>COL 1, ROW 18 text - formula derived</TD><TD>COL 2, Row 18 text - with Cond format</TD><TD>C4, R 18 text</TD><TD>C6, R 18 text</TD><TD>C8, R 18 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>COL 1, ROW 19 text - formula derived</TD><TD>COL 2, Row 19 text - with Cond format</TD><TD>C4, R 19 text</TD><TD>C6, R 19 text</TD><TD>C8, R 19 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>COL 1, ROW 20 text - formula derived</TD><TD>COL 2, Row 20 text - with Cond format</TD><TD>C4, R 20 text</TD><TD>C6, R 20 text</TD><TD>C8, R 20 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>COL 1, ROW 21 text - formula derived</TD><TD>COL 2, Row 21 text - with Cond format</TD><TD>C4, R 21 text</TD><TD>C6, R 21 text</TD><TD>C8, R 21 text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>COL 1, ROW 22 text - formula derived</TD><TD>COL 2, Row 22 text - with Cond format</TD><TD>C4, R 22 text</TD><TD>C6, R 22 text</TD><TD>C8, R 22 text</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I am going to try and bodgy something up over the weekend if I get time but would appreciate any help what-so-ever in achieving this or even with suggestions on how to do it better.

Thanks very much for taking the time to read this far and in advance for any help you provide.

Regards,
Darren

 

[AusSteelMan]

bump...

Any takers?

Thanks,
Darren

 

[Norie]

Darren

How about this?

A combobox with the 2 'default' sets and a Custom... item and a listbox with all the column headings from the raw data.

The user selects either one of the defaults and the columns for it are automatically selected in the listbox, or they select Custom and they can pick the columns they want.

Once they've made their choices the data is transferred to a new worksheet.

Is that the sort of thing that might work for you?

 

[AusSteelMan]

Hi Norie,

Thanks, that sounds like it would work if I understand it correctly.

The listbox is shown but pre-selected if a default is chosen, or do you mean the columns are "hard" coded and the listbox is only shown and selectable when the 3rd option is picked?

Regards,
Darren

 

[Norie]

Darren

The listbox would be shown from the start.

Picking one of the existing sets from the dropdown would just highlight the columns for the set.

If the user wants to manually change what's been selected they would need to choose the Custom option.

When they do that you have 2 basic choices of how to proceed - keep the existing selections in the combobox and allow the user to change them if they want, or
clear all the selections.

If you went for choice 1 it would allow the user to select all the columns for a set and add any other columns they might need, or remove any they don't need, for a specific task.

You could even take it further by allowing the user the option to save their custom choice for future use.

The name and columns for the sets could be stored on a hidden worksheet or whatever.

Each time they open the form the combobox would be populated with the names of all the saved sets.

If you don't want anything so complicated you could just have a group of option buttons with an option each for set you mentioned and a third 'custom' option.

 

[AusSteelMan]

Norie,

That whole process sounds great - I didn't even think about saving a particular setup for future use!

However, now that it is getting quite fancy, will you please help me out with the coding?
If not, I may just be able to pull off the very simple version .

Thanks,
Darren

 

[Norie]

Something like this perhaps.

http://www.box.net/shared/3l0ri8091e

 

[AusSteelMan]

Hi Norie,

I have been trying to make this work but without success. Is the original an Excel 2007 or 2010 file? My work 2000 version tried to convert it for me. The sheet opened but the button did not work. I tried several things including assigning the button to open the userform, but have had no luck.

If you still have the file, would you please repost to Box.net as an older version format. (I tried at home last night on 2003, but same thing happened)

Thanks very much though. From what I can see, it looks great.

Thanks,
Darren

 

[Norie]

Darren

Sorry about that, keep on forgetting to save as xls rather than xlsm.

I only recently started using 2010 but I usually have no problems opening files I create there in 2000.

Anyway, I'll save it as Excel 97-2003 and that should work, fingers crossed.

http://www.box.net/shared/fd4mtf6hng

By the way, you might have been able to at least access the form by opening the VBE (Alt+F11).

PS I'd be interested to know if it works OK on earlier versions - I've removed Excel 2000 from this machine so can't check myself.

 

[AusSteelMan]

Norie,

It is fantastic thanks. I would, however, like to PasteSpecial...Values rather than a simple Copy...Paste, so that the destination cells do not contain any of the formulas, just the resultant value.

I looked at the form code for the Transfer, but dont see a Paste that I could change, just a ".Copy rngDst"

Will you help out again please.

As for the 2007/10 version working in 2000, I am going to persist in converting and then making it work again (so I can keep learning of course). I'll post here if I get it to work.
ps. I am sure it should work, I think it is just my ability to do so

But thanks again, it is just what I was looking for...and more. I will adapt to my real sheet so I can save more time in future.

Regards,
Darren