Macro VBA code to copy content from one worksheet to another

[Xocial]

Hello. I have various worksheets in a workbook, each with 100 listed items
(B is description, C is price, D is availability - B1:B100, C1:C100,
D1100). I would to add checkboxes along A (A1:A100) where I can check, and
each time I check an item, that whole item with the various colums move to
another worksheet - lets call that worksheet SelectedCart (when I deselect an
item, it will disappear from SelectedCart). Also, I would each item added to
SelectedCart to be highlighted in different color (not sure if I should just
use Coditional Formatting).

I have a code that adds checkboxes and upon checking it adds date, but I am
not sure how I can do what I need (as listed above). Here is the code I have:

=================

Sub Process_CheckBox(pObject)

Dim LRow As Integer
Dim LRange As String

'Find row that checkbox resides in
LRow = pObject.TopLeftCell.Row
LRange = "B" & CStr(LRow)

'Change date in column B, if checkbox is checked
If pObject.Value = True Then
ActiveSheet.Range(LRange).Value = Date

'Clear date in column B, if checkbox is unchecked
Else
ActiveSheet.Range(LRange).Value = Null
End If

End Sub



Private Sub CheckBox1_Click()
Process_CheckBox CheckBox1
End Sub

Private Sub CheckBox2_Click()
Process_CheckBox CheckBox2
End Sub

Private Sub CheckBox3_Click()
Process_CheckBox CheckBox3
End Sub

Private Sub CheckBox4_Click()
Process_CheckBox CheckBox4
End Sub

Private Sub CheckBox5_Click()
Process_CheckBox CheckBox5
End Sub

Private Sub CheckBox6_Click()
Process_CheckBox CheckBox6
End Sub

Private Sub CheckBox7_Click()
Process_CheckBox CheckBox7
End Sub

=================

Any ideas where I can begin looking to do what I need? Thank you.

 

[Bob Phillips]

Here is one simple way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
.Offset(0, 1).Resize(, 3).Copy _
Worksheets("Sheet2").Range("A" & .Row)
Worksheets("Sheet2").Rows(.Row).Interior.ColorIndex = 36
.Value = "a"
.Font.Name = "Marlett"
Else
Worksheets("Sheet2").Rows(.Row).ClearContents
Worksheets("Sheet2").Rows(.Row).Interior.ColorIndex =
xlColorIndexNone
.Value = ""
End If
.Offset(0, 1).Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Xocial]

Hello,
Does this code replaces or is in addition to the code I posted? Do I put
the module in the first worksheet or all the worksheets i have? I have 5 or
6 worksheets with inventory, then check the ones i want from each worksheet
that transfer over to the last worksheet which I called SelectedCart.

Thank you!

 

[Bob Phillips]

That code is intended for just the one sheet, and instead of yours.

If you are trying to off-load many sheets onto one, should then just go to
the end of what is there. If so, how will we identify where it is on a
de-select?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Xocial]

Hello,
I tried your code in a blank workbook and nothing happened. I think I am
doing something wrong as I am not very seasoned in VBA or using it in Excel.

In essence, here is the layout of what I am trying to do (assuming it is
possible).

WorkSheet 1: Summary Page I created (done).
Worksheet 2: Inventory Type 1 that has 100 items (but may up a lot more)
Worksheet 3: Inventory Type 2 that has 100 items (but may up a lot more)
Worksheet 4: Inventory Type 3 that has 100 items (but may up a lot more)
Worksheet 5: Inventory Type 4 that has 100 items (but may up a lot more)
Worksheet 6: Inventory Type 5 that has 100 items (but may up a lot more)
Worksheet 7: Inventory Type 6 that has 100 items (but may up a lot more)
Worksheet 8: Inventory Type 7 that has 100 items (but may up a lot more)
Worksheet 9: This is the SelectedCart that will have the copied items that I
will check from worksheet2 - worksheet8.

The worksheets that have the inventory have various columns listed in B11,
and go on from there, B22, B33, et cetera.

I would like the checked items in SelectedCart to have rotating colors (that
I will select). Is this possible to do in Excel?

Also, if I need to add or delete a worksheet, what part of the code do I
need to amend?

Thank you.