how do I make a form that copies data and makes a new spreadsheet? 1

[rfvedcwsxqaz]

I have learned a bit about vb recently and have been using it for my job, I still have questions and dont know how to do many things yet.
I appreciate your time and efforts and hopefully I can figure out how to make this procedure work.

This is all taking place in vb6 and excel 2003.

I am making a userform that is activated once the user highlights a bit of spreadsheet and clicks a button on the command bar, here is what I want it to do:

1. (copy data from the spreadsheet
this is tricky because I am trying to have it be a conditional situation where the user highlights a place on the spreadsheet and whatever that place is at the moment, it will get copied to a variable on the form.
There are 8 fields to highlight and copy, two with info that wont be copied onto the new spreadsheet but will be used to update fields and make the filename.)

2. make a new spreadsheet:
a. the filename is made from cell values in the 'from' and 'to' columns and the date.

b. new spreadsheet needs to have a template section from a7:f7 that has 4 fields that will be filled in from the fields on the form.

c. copied data is to be moved from old spreadsheet to new spreadsheet to cell a8. There should be 6 columns that will be filled with data.

3. on the form there will be a browse button to save the file in a location specified by user and 'last saved location' name should be saved to a textbox.


I read about a browse button here that I would like to use, but I have to adapt it so it can be used in the 'browse' button.

http://www.tek-tips.com/viewthread.cfm?qid=1396007

I dont even know if it is possible to copy a user-specified range. It seems that it should be, because when a user highlights an area, that area is being held in memory as a position... I tried passing these values to another sheet but it wasnt successful. I tried to dim 'selection' as string and pass it into a variable, but I am new to variables and this project is a big experience for me in vb.

I also am still learning about how to make values in a field pass to another field on a spreadsheet.

Any examples of how to do any of this is much appreciated.

 

[SkipVought]

Hi,

It seems that you do not want to COPY the SELECTION and then PASTE it somewhere else.

Rather, it seems that you want to take specific VALUES from within the SELECTION and MAP them in various ways to cells in another sheet in another workbook, and to values, for the purpose of saving the new workbook.

Am I close, or off base?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[rfvedcwsxqaz]

Correct.

Within the selection area, item will be mapped to item, style to style, description to description, color to color, size to size, and quantity to quantity.

Also highlighted will be the 'to' and 'from' values, of which we only need the first value of each to name the file.

This is indeed a mapping situation. But it gets so complicated because I cant tell the program where it is going to occur... except for the fact that highlighting is involved. Because of that, I think that its possible to locate the selection programatically.

 

[SkipVought]

"...once the user highlights a bit of spreadsheet and clicks a button ..."

"...I cant tell the program where it is going to occur..."

It is where the user selects, is it not?

But what happens when the user selects MORE or LESS or in a DIFFERENT RANGE than you anticipate?





Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[rfvedcwsxqaz]

Ok. So everything was quite confusing before I put this into practice.

Im interested in having a dynamic selection process that the user can select a contiguous set of cells and the program will deal with routing the right values to the right columns.

I think that must be possible...

To circumvent that, I was thinking about just having an input box where the user can specify what range they want, and then the 'Mapping' of values can occur.

The program does not need much validation as it will be used by users who will be using the files they just made right after creating them.

I have made the code as much as I can understand how to make at this point.

The file can be downloaded here:


I had some issues with passing values from form to new spreadsheet and I dont know why...

I appreciate your help Skip.

 

[SkipVought]

I can't find it.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[rfvedcwsxqaz]

Skip, you have the knowledge!
Here:

http://www.tek-tips.com/viewthread.cfm?qid=202361

So, I want:
1. part of program that selects data
2. part of program that makes new sheet
3. part of program that sets up new sheet
3. part of program that updates new sheet
4. part of program that saves new file

I am curious. If I can set it up so that worksheet_change deals with the target, target range being the cells that are recently selected by user.

From there, the target does not need to be specified by me programatically, but the sheet will report the changes.

From there I envision the result will give me the changes for 8 columns, and an unspecified number of rows.
From there, I can organize and map the values into the columns on the new sheet.
I assume I could accomplish the passing of the data with a variable for each column.

This is all speculation. What do you think?
Skip, I can send you my file.