Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet amalgamation
hello everybody,
im trying to use macros to copy three separate worksheets into one, with each one added at the click of a button each time the user has finished entering data. I have tried recording the copying and pasting of each worksheet into the main one using a macro then assigning it to a button, but i have drop down and check boxes and when pasted these do not correlate. I want to know how to transfer them all together without any alignment problems. Again, any help much appreciated, you will be helping a very frustrated worker! Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet amalgamation
More detail please. You say you have a problem with alignment ... in what way?
Are the three sheets being added to while retaining information that has already been transferred to the one worksheet? At what point in the process does the copy from the 3 data entry sheets back to the main sheet take place? End of one row of data entry - or can it take place after several rows of data have been entered? If there are differences in column locations for the data, we need to know that also. If it is a straight "column A from sheetA/B/C needs to go to column A on Main Sheet, along with B to B, C to C all the way to AB" that's fine, and it is just as fine if column A from sheetsA/B/C needs to go into Column Z on Main sheet while B from A/B/C sheets needs to go into F on Main - or some variety of this, but we need to know how the columns from the 3 separate sheets "map" to columns on the main sheet to give you a good answer. "andrewbt" wrote: hello everybody, im trying to use macros to copy three separate worksheets into one, with each one added at the click of a button each time the user has finished entering data. I have tried recording the copying and pasting of each worksheet into the main one using a macro then assigning it to a button, but i have drop down and check boxes and when pasted these do not correlate. I want to know how to transfer them all together without any alignment problems. Again, any help much appreciated, you will be helping a very frustrated worker! Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet amalgamation
The process of copying the sheets into one takes place after all of the data
has been entered. Its basically three order forms from separate worksheeets that come togather to form one larger complete order form. I want my users to fill each one in in a logical order, e.g the font order form sheet is filled in and added first to the final sheet, the second one after and below that e.t.c. All workbooks will be open at the same time so the macros arent the problem. I want the worksheets to paste in beneath each other(i thought this would be easy!), not to specific cell references, just beneath where the last one finishes. By alignment i mean that when the first worksheet is copied over the form elements duplicate/ move out of line, and i end up with large drop down boxes in the background getting in the way. I hope you can help in any way, its just that ive ran out of ideas. Thanks Andrew "JLatham" wrote: More detail please. You say you have a problem with alignment ... in what way? Are the three sheets being added to while retaining information that has already been transferred to the one worksheet? At what point in the process does the copy from the 3 data entry sheets back to the main sheet take place? End of one row of data entry - or can it take place after several rows of data have been entered? If there are differences in column locations for the data, we need to know that also. If it is a straight "column A from sheetA/B/C needs to go to column A on Main Sheet, along with B to B, C to C all the way to AB" that's fine, and it is just as fine if column A from sheetsA/B/C needs to go into Column Z on Main sheet while B from A/B/C sheets needs to go into F on Main - or some variety of this, but we need to know how the columns from the 3 separate sheets "map" to columns on the main sheet to give you a good answer. "andrewbt" wrote: hello everybody, im trying to use macros to copy three separate worksheets into one, with each one added at the click of a button each time the user has finished entering data. I have tried recording the copying and pasting of each worksheet into the main one using a macro then assigning it to a button, but i have drop down and check boxes and when pasted these do not correlate. I want to know how to transfer them all together without any alignment problems. Again, any help much appreciated, you will be helping a very frustrated worker! Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet amalgamation
It sounds like you have a process that works to a point: it is able to copy
an area of a sheet in one workbook and paste it into another area in another sheet in another workbook. At this point I'd make one suggestion to try that may clear up the alignment problem. In your code, or process, don't do a plain copy and paste; instead, use copy and Paste Special with the [Values] option. You can record a new macro while doing it once to get the proper syntax, but basically where you probably have something like ActiveSheet.Paste in your code now, you're going to have a line similar to this: Selection.PasteSpecial Paste:=xlPasteValues If you're using objects created with SET commands to reference the source and/or destination ranges, then that object would replace the Selection portion of the paste special statement above. If you don't know what I'm talking about here, don't worry, probably doesn't apply to your situation. If this doesn't help, let us know, and perhaps paste a copy of the code that's doing the copy/paste operation and we can maybe offer other suggestions. "andrewbt" wrote: The process of copying the sheets into one takes place after all of the data has been entered. Its basically three order forms from separate worksheeets that come togather to form one larger complete order form. I want my users to fill each one in in a logical order, e.g the font order form sheet is filled in and added first to the final sheet, the second one after and below that e.t.c. All workbooks will be open at the same time so the macros arent the problem. I want the worksheets to paste in beneath each other(i thought this would be easy!), not to specific cell references, just beneath where the last one finishes. By alignment i mean that when the first worksheet is copied over the form elements duplicate/ move out of line, and i end up with large drop down boxes in the background getting in the way. I hope you can help in any way, its just that ive ran out of ideas. Thanks Andrew "JLatham" wrote: More detail please. You say you have a problem with alignment ... in what way? Are the three sheets being added to while retaining information that has already been transferred to the one worksheet? At what point in the process does the copy from the 3 data entry sheets back to the main sheet take place? End of one row of data entry - or can it take place after several rows of data have been entered? If there are differences in column locations for the data, we need to know that also. If it is a straight "column A from sheetA/B/C needs to go to column A on Main Sheet, along with B to B, C to C all the way to AB" that's fine, and it is just as fine if column A from sheetsA/B/C needs to go into Column Z on Main sheet while B from A/B/C sheets needs to go into F on Main - or some variety of this, but we need to know how the columns from the 3 separate sheets "map" to columns on the main sheet to give you a good answer. "andrewbt" wrote: hello everybody, im trying to use macros to copy three separate worksheets into one, with each one added at the click of a button each time the user has finished entering data. I have tried recording the copying and pasting of each worksheet into the main one using a macro then assigning it to a button, but i have drop down and check boxes and when pasted these do not correlate. I want to know how to transfer them all together without any alignment problems. Again, any help much appreciated, you will be helping a very frustrated worker! Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Discussion (Misc queries) | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Worksheet Functions | |||
Automatic amalgamation | Excel Worksheet Functions | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) |