VBA for exporting to another predefined workbook

[Wowdude]

Hi,

I'm putting together some workbooks for my company and looking into how to use VBA to send out work to another workbook.

I already have the code to copy and paste the code into a specified workbook (by destination), but i'm looking for a way to have a selectable drop down menu and by picking a name and then using the macro, the selected data will be cut and pasted into the workbook of the same name as the user picked.

Any ideas?

 

[Wowdude]

I have used the following code and it started working perfectly.

It simply runs through a few If statements to see what the C1 cell contains (drop down menu) and uses the relevant workbook linked to that process.

However, now when it gets to the "Paste" section, I get the Run-time error 1004.

Private Sub CommandButton21_Click()

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    

Dim myRange As Range
Set myRange = Selection
 wb = ActiveWorkbook.Name
 

   If Cells(1, "C") = "Avtar" Then
     Workbooks.Open "C:\Users\ben.hubery\Desktop\Avtar.xlsx"
     Windows(wb).Activate
     Selection.Cut 'define the range to copy

     Windows("Avtar.xlsx").Activate
     Sheets("Sheet1").Select
     Workbooks("Avtar").Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlNone, _
     SkipBlanks:=False, _
     Transpose:=False
     Application.CutCopyMode = False
    End If
 

    If Cells(1, "C") = "Ben" Then
     Workbooks.Open "C:\Users\ben.hubery\Desktop\Ben.xlsx"
     Windows(wb).Activate
     Selection.Cut 'define the range to copy

     Windows("Ben.xlsx").Activate
     Sheets("Sheet1").Select
     Workbooks("Ben").Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlNone, _
     SkipBlanks:=False, _
     Transpose:=False
     Application.CutCopyMode = False
    End If
 

    If Cells(1, "C") = "Farai" Then
     Workbooks.Open "C:\Users\ben.hubery\Desktop\Farai.xlsx"
     Windows(wb).Activate
     Selection.Cut 'define the range to copy
    
     Windows("Farai.xlsx").Activate
     Sheets("Sheet1").Select
     Workbooks("Farai").Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlNone, _
     SkipBlanks:=False, _
     Transpose:=False
     Application.CutCopyMode = False
    End If
     

    If Cells(1, "C") = "Sophie" Then
     Workbooks.Open "C:\Users\ben.hubery\Desktop\Sophie.xlsx"
     Windows(wb).Activate
     Selection.Cut 'define the range to copy
    
     Windows("Sophie.xlsx").Activate
     Sheets("Sheet1").Select
     Workbooks("Sophie").Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlNone, _
     SkipBlanks:=False, _
     Transpose:=False
     Application.CutCopyMode = False
    End If
 

 Workbooks(wb).Activate
 
End Sub