Delete Copied Data

[Beale]

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:

[VBA]
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[/VBA]

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!

[mdmackillop]

Hi Beale,
Welcome to VBAX.
Can you post a sample workbook?. Use Manage Attachments in the Go Advanced reply section.

[Beale]

Hey mdmackillop, Thanks for your help!

I've attached a simplified version of my spreadsheet - I've tried to hide or remove anything unrelated to this process. Its still work in progress so excuse the mess!

I basically need a link between the "site" (Site One, Site Two) worksheets and the "company" worksheets (C1, C2) based on the input on the "site" worksheet. The more I think and research into it the more advanced filters might be the way forward instead of using this code, although I'm not sure whether that can be done over multiple worksheets?...I'm open to suggestions!

The "site" worksheets will expand to 50-100 sheets and the company worksheets will expand to 10.

[mdmackillop]

Sorry,
I'm not quite clear what you're after.