Copy corresponding row to another sheet when select in a dropdown list

[WXM86]

Good Evening,

Am looking for someone to help with code am creating a spreadsheet with 40 sheets with a dropdown with 1-40 when i select the number in Colum G i want the data to copy to the sheet number but code need to be if am to select a different number from the drop down this is change , can this be done

 

[Bill Rockenbach]

when i select the number in Colum G i want the data (what data?) (Override data in sheet copied to?)
to copy to the sheet number (to the sheet number selected?)
but code need to be if am to select a different number from the drop down this is change (Do you mean copy only id not copied before? Please reword)

 

[WXM86]

when i select the number in Colum G i want the data (what data?) (Override data in sheet copied to?)
to copy to the sheet number (to the sheet number selected?)
but code need to be if am to select a different number from the drop down this is change (Do you mean copy only id not copied before? Please reword)

So in Colum G i have a drop down with numbers 1 -40 and have sheets 1-40 i want a code so when we put 4 in the location in column G that information copies to the sheet number 4 but if down the line we change the location to like 10 we need the data from sheet 4 to be deleted and it moved to sheet 10 hope it makes sense

 

[Bill Rockenbach]

Private Sub cmbSheetList_Change()
'Copy dataset from the MasterSheet to the destination sheet
'using an activex dropdown box named cmbSheetList on the MasterSheet using cell G1 as
'a sheet number control
'Sample dataset range"A1:B2"

    Dim SelectedSheet As Integer

    SelectedSheet = cmbSheetList

    'Check if data already copied to SelectedSheet
    If Sheets("MasterSheet").Range("G1") = SelectedSheet Then
        MsgBox "Data currently in Sheet " & SelectedSheet & ".", vbInformation
        Exit Sub
    Else
        'Remove datset from sheet it is now in
        If Range("G1") > 0 Then Sheets("Sheet" & Range("G1")).Range("A1:B2").Clear

        'Copy dataset to SelectedSheet
        Sheets("MasterSheet").Range("A1:B2").Copy Sheets("Sheet" & SelectedSheet).Range("A1")
        
        Range("G1") = SelectedSheet
    End If

End Sub

 

[WXM86]

Private Sub cmbSheetList_Change()
'Copy dataset from the MasterSheet to the destination sheet
'using an activex dropdown box named cmbSheetList on the MasterSheet using cell G1 as
'a sheet number control
'Sample dataset range"A1:B2"

    Dim SelectedSheet As Integer

    SelectedSheet = cmbSheetList

    'Check if data already copied to SelectedSheet
    If Sheets("MasterSheet").Range("G1") = SelectedSheet Then
        MsgBox "Data currently in Sheet " & SelectedSheet & ".", vbInformation
        Exit Sub
    Else
        'Remove datset from sheet it is now in
        If Range("G1") > 0 Then Sheets("Sheet" & Range("G1")).Range("A1:B2").Clear

        'Copy dataset to SelectedSheet
        Sheets("MasterSheet").Range("A1:B2").Copy Sheets("Sheet" & SelectedSheet).Range("A1")
       
        Range("G1") = SelectedSheet
    End If

End Sub
[/CODE
[/QUOTE]

[QUOTE="Bill Rockenbach, post: 6037869, member: 117065"]
Thanks bill Sorry to be a pain do i need to change part of the code or
[/QUOTE]

 

[WXM86]

Hi Bill,

Thank you do i need to edit part of the code for mine as getting an error.

 

[Bill Rockenbach]

WXM86, my code is using cell G1 to store the sheet number with the current copied data. Remove all data validation from cell G1 or whichever cell you used if you changed the code.

 

[WXM86]

Thank you i would say i understand that but i don't sorry.