I would like to copy particular cells from nine different worksheets and
paste (value only) to another sheet named €œPrint€�. All the sheets are within
one workbook.
The contents of the cells I need to copy are activated by check boxes. The
check box cell links are all in column O.
For example, if O10, O15, and O135 are TRUE, I need ranges (D10:H10),
(D15:H15), and (D135:H135) to be copied and then pasted as values only to the
€œPrint€� worksheet, into cells (A1:E1), (A2:E2), (A3:E3), and so on.
I would like to be able to tick the appropriate check boxes and then perhaps
have a button named €œexport€� to run the copy/paste macro.
Please note that while the check box cell links are in named ranges, there
are many empty cells between the ranges, so cells containing the word €œTRUE€�
in column O would need to be searched for, either from O1 to the bottom, or
perhaps a predefined range like (O1:O300) if thats easier or more efficient.
Only the active worksheet needs to be searched.
Ive looked long and hard for a solution in this discussion group, and Ive
experimented with lots of the answers I found, but I dont have adequate
skill to adapt them to what I need.
I would really appreciate advice on this.
I hope Im not asking too much.

Thank you
Kind regards
Clinton

Clinton,
Get in touch with me via email at (remove spaces)
Help From @ JLatham Site.com
It's obvious that there are more cells to be considered, and I think the
solution will be easier to arrive at by direct communication so you can
provide a complete list of cells involved, worksheet names, etc.

If you can, attach a copy of the workbook to the email, and remind me either
of your requirements or provide a link back to your original post so I can
'remember' what the discussion is about.

Eventually we will need a complete set of rules:
combinations of entries in the O column defined to indicate their status and
when that status is encountered what cells to copy from/to. If you've got
that already written up, so much the better.

"Clinton W" wrote:

I would like to copy particular cells from nine different worksheets and
paste (value only) to another sheet named €œPrint€�. All the sheets are within
one workbook.
The contents of the cells I need to copy are activated by check boxes. The
check box cell links are all in column O.
For example, if O10, O15, and O135 are TRUE, I need ranges (D10:H10),
(D15:H15), and (D135:H135) to be copied and then pasted as values only to the
€œPrint€� worksheet, into cells (A1:E1), (A2:E2), (A3:E3), and so on.
I would like to be able to tick the appropriate check boxes and then perhaps
have a button named €œexport€� to run the copy/paste macro.
Please note that while the check box cell links are in named ranges, there
are many empty cells between the ranges, so cells containing the word €œTRUE€�
in column O would need to be searched for, either from O1 to the bottom, or
perhaps a predefined range like (O1:O300) if thats easier or more efficient.
Only the active worksheet needs to be searched.
Ive looked long and hard for a solution in this discussion group, and Ive
experimented with lots of the answers I found, but I dont have adequate
skill to adapt them to what I need.
I would really appreciate advice on this.
I hope Im not asking too much.

Thank you
Kind regards
Clinton

Hi.
I don't know if this is what you need you will have to create a button and
assign the macro to it the macro would need to be on each data sheet.


Sub TransferDataToList()
'
' Each item will hold up to 200 entries
' You could have 300 checkboxes ticked
' Set the number to whatever you want
Dim Item1(300)
Dim Item2(300)
Dim Item3(300)
Dim Item4(300)
Dim Item5(300)

' Select the cell at the top of your Check box List
Range("O1").Select

'Get True Cell list Down= offset down

For Down = 0 To 299 ' set to what ever number you require. the number of
Checkboxes ?
If ActiveCell.Offset(Down, 0) = True Then Item1(Down) =
ActiveCell.Offset(Down, -11).Value
If ActiveCell.Offset(Down, 0) = True Then Item2(Down) =
ActiveCell.Offset(Down, -10).Value
If ActiveCell.Offset(Down, 0) = True Then Item3(Down) =
ActiveCell.Offset(Down, -9).Value
If ActiveCell.Offset(Down, 0) = True Then Item4(Down) =
ActiveCell.Offset(Down, -8).Value
If ActiveCell.Offset(Down, 0) = True Then Item5(Down) =
ActiveCell.Offset(Down, -7).Value:
Next Down
'
Sheets("PRINT").Select

'Get the position for the data to be entered into.
Position = Range("F1").Value
' "F1" can be any cell on the PRINT Sheet That will not be printed
' the Cell"F1" will have to contain the formula =COUNTA(A1:A300)
'this counts cells in your print list that are NOT empty

Range("A1").Select ' the first cell on the left in your Print List

For t = 0 To 300

ActiveCell.Offset(Position, 0) = Item1(t)
ActiveCell.Offset(Position, 1) = Item2(t)
ActiveCell.Offset(Position, 2) = Item3(t)
ActiveCell.Offset(Position, 3) = Item4(t)
ActiveCell.Offset(Position, 4) = Item5(t)
Position = Range("F1").Value
Next t

End Sub

Hope this helps.

Regards Keith B

"Clinton W" wrote:

I would like to copy particular cells from nine different worksheets and
paste (value only) to another sheet named €œPrint€�. All the sheets are within
one workbook.
The contents of the cells I need to copy are activated by check boxes. The
check box cell links are all in column O.
For example, if O10, O15, and O135 are TRUE, I need ranges (D10:H10),
(D15:H15), and (D135:H135) to be copied and then pasted as values only to the
€œPrint€� worksheet, into cells (A1:E1), (A2:E2), (A3:E3), and so on.
I would like to be able to tick the appropriate check boxes and then perhaps
have a button named €œexport€� to run the copy/paste macro.
Please note that while the check box cell links are in named ranges, there
are many empty cells between the ranges, so cells containing the word €œTRUE€�
in column O would need to be searched for, either from O1 to the bottom, or
perhaps a predefined range like (O1:O300) if thats easier or more efficient.
Only the active worksheet needs to be searched.
Ive looked long and hard for a solution in this discussion group, and Ive
experimented with lots of the answers I found, but I dont have adequate
skill to adapt them to what I need.
I would really appreciate advice on this.
I hope Im not asking too much.

Thank you
Kind regards
Clinton

Thank you for your reply, Keith.
For some reason I haven't been able to make this work, but I have found a
different solution to my question. I do appreciate your time and effort.
Regards
Clinton

"Keith B" wrote:


Hi.
I don't know if this is what you need you will have to create a button and
assign the macro to it the macro would need to be on each data sheet.


Sub TransferDataToList()
'
' Each item will hold up to 200 entries
' You could have 300 checkboxes ticked
' Set the number to whatever you want
Dim Item1(300)
Dim Item2(300)
Dim Item3(300)
Dim Item4(300)
Dim Item5(300)

' Select the cell at the top of your Check box List
Range("O1").Select

'Get True Cell list Down= offset down

For Down = 0 To 299 ' set to what ever number you require. the number of
Checkboxes ?
If ActiveCell.Offset(Down, 0) = True Then Item1(Down) =
ActiveCell.Offset(Down, -11).Value
If ActiveCell.Offset(Down, 0) = True Then Item2(Down) =
ActiveCell.Offset(Down, -10).Value
If ActiveCell.Offset(Down, 0) = True Then Item3(Down) =
ActiveCell.Offset(Down, -9).Value
If ActiveCell.Offset(Down, 0) = True Then Item4(Down) =
ActiveCell.Offset(Down, -8).Value
If ActiveCell.Offset(Down, 0) = True Then Item5(Down) =
ActiveCell.Offset(Down, -7).Value:
Next Down
'
Sheets("PRINT").Select

'Get the position for the data to be entered into.
Position = Range("F1").Value
' "F1" can be any cell on the PRINT Sheet That will not be printed
' the Cell"F1" will have to contain the formula =COUNTA(A1:A300)
'this counts cells in your print list that are NOT empty

Range("A1").Select ' the first cell on the left in your Print List

For t = 0 To 300

ActiveCell.Offset(Position, 0) = Item1(t)
ActiveCell.Offset(Position, 1) = Item2(t)
ActiveCell.Offset(Position, 2) = Item3(t)
ActiveCell.Offset(Position, 3) = Item4(t)
ActiveCell.Offset(Position, 4) = Item5(t)
Position = Range("F1").Value
Next t

End Sub

Hope this helps.

Regards Keith B

"Clinton W" wrote:

I would like to copy particular cells from nine different worksheets and
paste (value only) to another sheet named €œPrint€�. All the sheets are within
one workbook.
The contents of the cells I need to copy are activated by check boxes. The
check box cell links are all in column O.
For example, if O10, O15, and O135 are TRUE, I need ranges (D10:H10),
(D15:H15), and (D135:H135) to be copied and then pasted as values only to the
€œPrint€� worksheet, into cells (A1:E1), (A2:E2), (A3:E3), and so on.
I would like to be able to tick the appropriate check boxes and then perhaps
have a button named €œexport€� to run the copy/paste macro.
Please note that while the check box cell links are in named ranges, there
are many empty cells between the ranges, so cells containing the word €œTRUE€�
in column O would need to be searched for, either from O1 to the bottom, or
perhaps a predefined range like (O1:O300) if thats easier or more efficient.
Only the active worksheet needs to be searched.
Ive looked long and hard for a solution in this discussion group, and Ive
experimented with lots of the answers I found, but I dont have adequate
skill to adapt them to what I need.
I would really appreciate advice on this.
I hope Im not asking too much.

Thank you
Kind regards
Clinton