Beale
06-08-2009, 03:15 AM
Hey all!
I was hoping if you could let me know if something I need to do is possible...
Upon ticking a box I need the code to copy a range and paste it to the next empty cell of a worksheet defined by a drop down box. I have (rather surprisingly!) worked out how to achieve this thanks to various codes on this site:
Sub Confirm_Click()
'this macro copies and paste data from one sheet to another. it is linked to the drop down box
Dim A As String
Dim B As String
Dim C As String
A = Worksheets("Data").Cells(4, "B").Value
B = Worksheets("Data").Cells(5, "B").Value
C = Worksheets("Data").Cells(6, "B").Value
If Confirm.Value = True Then
'This area contains the things you want to happen
'when the checkbox is not depressed
If UCase(ActiveSheet.Cells(8, 13).Value) = (A) Then
ActiveSheet.Range("Alpha").Copy Sheets(A).Cells(TargetRow(Sheets(A), 1), 1)
ElseIf UCase(ActiveSheet.Cells(8, 13).Value) = (B) Then
ActiveSheet.Range("Alpha").Copy Sheets(B).Cells(TargetRow(Sheets(B), 1), 1)
ElseIf UCase(ActiveSheet.Cells(8, 13).Value) = (C) Then
ActiveSheet.Range("Alpha").Copy Sheets(C).Cells(TargetRow(Sheets(C), 1), 1)
End If
MsgBox "Company Woksheet Updated"
Else
'This area contains the things you want to happen
'when the toggle button is depressed
MsgBox "Box not checked"
End If
End Sub
The next step of the code needs to delete the data that was copied if the box is unticked. This situation can occur after subsequent data has been added to the target sheet. Is this possible?! I assume if it is I need to utilise named ranges somehow but I'm not sure how!
Thanks for any advice!
I was hoping if you could let me know if something I need to do is possible...
Upon ticking a box I need the code to copy a range and paste it to the next empty cell of a worksheet defined by a drop down box. I have (rather surprisingly!) worked out how to achieve this thanks to various codes on this site:
Sub Confirm_Click()
'this macro copies and paste data from one sheet to another. it is linked to the drop down box
Dim A As String
Dim B As String
Dim C As String
A = Worksheets("Data").Cells(4, "B").Value
B = Worksheets("Data").Cells(5, "B").Value
C = Worksheets("Data").Cells(6, "B").Value
If Confirm.Value = True Then
'This area contains the things you want to happen
'when the checkbox is not depressed
If UCase(ActiveSheet.Cells(8, 13).Value) = (A) Then
ActiveSheet.Range("Alpha").Copy Sheets(A).Cells(TargetRow(Sheets(A), 1), 1)
ElseIf UCase(ActiveSheet.Cells(8, 13).Value) = (B) Then
ActiveSheet.Range("Alpha").Copy Sheets(B).Cells(TargetRow(Sheets(B), 1), 1)
ElseIf UCase(ActiveSheet.Cells(8, 13).Value) = (C) Then
ActiveSheet.Range("Alpha").Copy Sheets(C).Cells(TargetRow(Sheets(C), 1), 1)
End If
MsgBox "Company Woksheet Updated"
Else
'This area contains the things you want to happen
'when the toggle button is depressed
MsgBox "Box not checked"
End If
End Sub
The next step of the code needs to delete the data that was copied if the box is unticked. This situation can occur after subsequent data has been added to the target sheet. Is this possible?! I assume if it is I need to utilise named ranges somehow but I'm not sure how!
Thanks for any advice!