Macro to copy data using button to worksheets based on drop down value

[vee_vee8]

Hello,

I was really hoping someone would be able to help me with a macro for this. I've attempted to review solutions in other forums but my vba knowledge is pretty basic so I'm out of my depth here.

I have a spreadsheet where information is added to a worksheet called 'Data Entry', information is added in cells, C11:C13, C16:C19, C22:C26 and C29:C32 daily. There is a drop down at cell B9 which relates to the names of the different sheets. I was hoping to see if there was a way that when the drop-down option was selected that the data could be copied across with a button to the corresponding sheet?

Any advice would be greatly appreciated.

 

[kevin9999]

Are all your sheets - W1-D1, W1-D2 etc structured the same way; i.e. do the destination tables all start in row 30 (first header) in each of the sheets?

 

[vee_vee8]

Yes all of the sheets are structured the same

 

[kevin9999]

Try the following on a copy of your workbook.

Option Explicit
Sub VeeVee8()
    Dim ws As Worksheet, s As String, i As Long, exists As Boolean
    Set ws = Worksheets("Data Entry")
    s = ws.Range("B9")
    
    'Check if sheet exists
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = s Then
        exists = True
    End If
    Next i
    If Not exists Then
        MsgBox "The sheet " & s & " doesn't exist in this workbook"
        Exit Sub
    End If
    
    ws.Range("C10:C32").Copy Worksheets(s).Range("B30")
End Sub

 

[vee_vee8]

Try the following on a copy of your workbook.

Option Explicit
Sub VeeVee8()
    Dim ws As Worksheet, s As String, i As Long, exists As Boolean
    Set ws = Worksheets("Data Entry")
    s = ws.Range("B9")
   
    'Check if sheet exists
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = s Then
        exists = True
    End If
    Next i
    If Not exists Then
        MsgBox "The sheet " & s & " doesn't exist in this workbook"
        Exit Sub
    End If
   
    ws.Range("C10:C32").Copy Worksheets(s).Range("B30")
End Sub

Thank you so much! That worked perfectly. Really appreciate your help!!!

 

[kevin9999]

Happy to help & thank you for the feedback