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.

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.

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.

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.