Moving worksheets into a single workbook without copy/paste

[Filipzgela]

Hello,

i have another task that i seem to be lost on.
My company has excel for their timesheet tables. There is about 150 people who are going to filling them out weekly.
I have made the sheets and set them up with data validation and a few rules that have to be followed. But, to make sure the information is entered exactly as intended i needed to disable copy/paste (with VBA) on those workbooks.
Now, the issue is that i need to also add up all of those hours into one table. Definitely the easiest way i have found is to copy/paste (with VBA again) all of those to one excel workbook, and work with that in one workbook.
The issue is, is that i will not be able to do that anymore since copy/paste is disabled in timesheet workbooks.
The timesheets are consisted of 3 sheets, but only 1 of those are really what needs to be copied.
My question is: is there a way to either disable the VBA that's stopping copy/paste in the timesheets from the workbook i am copying them to, or move the worksheets some other way into a singular workbook? I need the worksheets to stay in their previous excel's aswell as be "moved" or copied. It would be extremely tedious to do this by hand every week, since there are a lot of them.
I have previously used this code, incase it helps(i obviously had to select the workbooks :
Sub Copying_sheets()
Application.ScreenUpdating = False
Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
Set desWB = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
If .Show = -1 Then
For Each vSelectedItem In .SelectedItems
Set srcWB = Workbooks.Open(vSelectedItem)
srcWB.Sheets(1).Copy after:=desWB.Sheets(desWB.Sheets.Count)
srcWB.Close False
Next
Else
End If
End With
Application.ScreenUpdating = True
End Sub

Also, this is the code i've used for blocking copy/paste:
Private Sub Workbook_Activate()

Application.CutCopyMode = False

Application.OnKey "^c", ""

Application.CellDragAndDrop = False

End Sub



Private Sub Workbook_Deactivate()

Application.CellDragAndDrop = True

Application.OnKey "^c"

Application.CutCopyMode = False

End Sub



Private Sub Workbook_WindowActivate(ByVal Wn As Window)

Application.CutCopyMode = False

Application.OnKey "^c", ""

Application.CellDragAndDrop = False

End Sub



Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

Application.CellDragAndDrop = True

Application.OnKey "^c"

Application.CutCopyMode = False

End Sub



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.CutCopyMode = False

End Sub



Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.OnKey "^c", ""

Application.CellDragAndDrop = False

Application.CutCopyMode = False

End Sub



Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Application.CutCopyMode = False

End Sub

 

[DanteAmor]

My question is: is there a way to either disable the VBA that's stopping copy/paste in the timesheets from the workbook i am copying them to

Use Application.EnableEvents = False
That way the events are turned off and you can copy and paste.
At the end of your macro you must enable events again.

Try:

Sub Copying_sheets()
  Application.ScreenUpdating = False
 
  Application.EnableEvents = False
 
  Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
  Set desWB = ThisWorkbook
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    .AllowMultiSelect = True
    If .Show = -1 Then
      For Each vSelectedItem In .SelectedItems
        Set srcWB = Workbooks.Open(vSelectedItem)
        srcWB.Sheets(1).Copy after:=desWB.Sheets(desWB.Sheets.Count)
        srcWB.Close False
      Next
    Else
    End If
  End With
 
  Application.EnableEvents = True
 
  Application.ScreenUpdating = True
End Sub

 

[Filipzgela]

Use Application.EnableEvents = False
That way the events are turned off and you can copy and paste.
At the end of your macro you must enable events again.

Try:

Sub Copying_sheets()
  Application.ScreenUpdating = False
 
  Application.EnableEvents = False
 
  Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
  Set desWB = ThisWorkbook
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    .AllowMultiSelect = True
    If .Show = -1 Then
      For Each vSelectedItem In .SelectedItems
        Set srcWB = Workbooks.Open(vSelectedItem)
        srcWB.Sheets(1).Copy after:=desWB.Sheets(desWB.Sheets.Count)
        srcWB.Close False
      Next
    Else
    End If
  End With
 
  Application.EnableEvents = True
 
  Application.ScreenUpdating = True
End Sub

Thank you so much, this works exactly as i needed it.