Macro VBA code to copy content from one worksheet to another
 

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,
D1:D100). 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.

Macro VBA code to copy content from one worksheet to another
 

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.ColorInde x = 36
.Value = "a"
.Font.Name = "Marlett"
Else
Worksheets("Sheet2").Rows(.Row).ClearContents
Worksheets("Sheet2").Rows(.Row).Interior.ColorInde x =
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" wrote in message
...
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,
D1:D100). 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.

Macro VBA code to copy content from one worksheet to another
 

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" wrote:

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.ColorInde x = 36
.Value = "a"
.Font.Name = "Marlett"
Else
Worksheets("Sheet2").Rows(.Row).ClearContents
Worksheets("Sheet2").Rows(.Row).Interior.ColorInde x =
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" wrote in message
...
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,
D1:D100). 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.

Macro VBA code to copy content from one worksheet to another
 

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" wrote in message
...
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" wrote:

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.ColorInde x = 36
.Value = "a"
.Font.Name = "Marlett"
Else
Worksheets("Sheet2").Rows(.Row).ClearContents
Worksheets("Sheet2").Rows(.Row).Interior.ColorInde x =
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" wrote in message
...
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,
D1:D100). 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.

Macro VBA code to copy content from one worksheet to another
 

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 B1:D1,
and go on from there, B2:D2, B3:D3, 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.