Copy Paste from one Workbook to another

B

buzz71023

Active Member
Joined
May 29, 2011
Messages
295
Office Version
  1. 2016
Platform
  1. Windows
I have the below code that is supposed to copy and paste from one workbook to another but on the workbook that the infomation is going to, I have a code that activates a userform when one of the cells is selected. So when the code below runs, it activates the userform. after I cancel or x out of the userform, I get an "Run-time '1004': PasteSpecial method of Range class failed" error.
Is there a way that I can copy this data from one sheet to another without "selecting" the cell and activating the userform?

Apologies.. I know it is a mess

VBA Code: 
        Workbooks("TEMP06_Operator Workbook").Activate
        rngDate.Copy
        Workbooks(NXTfilename).Activate
        DateTOrng.PasteSpecial xlPasteValues
      
        Workbooks("TEMP06_Operator Workbook").Activate
        rngStartTime.Copy
        Workbooks(NXTfilename).Activate
        StartTOrng.PasteSpecial xlPasteValues
      
        Workbooks("TEMP06_Operator Workbook").Activate
        rngEndTime.Copy
        Workbooks(NXTfilename).Activate
        EndTOrng.PasteSpecial xlPasteValues
      
        Workbooks("TEMP06_Operator Workbook").Activate
        rngCode.Copy
        Workbooks(NXTfilename).Activate
        CodeTOrng.PasteSpecial xlPasteValues
      
        Workbooks("TEMP06_Operator Workbook").Activate
        rngReason.Copy
        Workbooks(NXTfilename).Activate
        ReasonTOrng.PasteSpecial xlPasteValues
      
        Workbooks("TEMP06_Operator Workbook").Activate
        rngCrew.Copy
        Workbooks(NXTfilename).Activate
        CrewTOrng.PasteSpecial xlPasteValues
      
        Workbooks("TEMP06_Operator Workbook").Activate
        rngINT.Copy
        Workbooks(NXTfilename).Activate
        IntTOrng.PasteSpecial xlPasteValues
      
        Workbooks("TEMP06_Operator Workbook").Activate
        rngKeyword.Copy
        Workbooks(NXTfilename).Activate
        KeyworkTOrng.PasteSpecial xlPasteValues

This is the code that is on the paste portion that activates the userform.

VBA Code: 
If Not Application.Intersect(Target, Range("TimeLedger")) Is Nothing Then
frmTime1.Show
End If

Here is the whole code that I using.

VBA Code: 
Application.ScreenUpdating = False

Set shtJCO = Workbooks("TEMP06_Operator Workbook").sheets("JCO")
Set shtHours = Workbooks("TEMP06_Operator Workbook").sheets("Hours Sheet")
Set rngCurrPC = shtJCO.Range("G8")
Set rngCurrLOT = shtJCO.Range("C4")
Set rngPLINE = shtJCO.Range("G10")
Set rngNxtPC = shtJCO.Range("k17")
Set rngNxtLOT = shtJCO.Range("k18")
Set shtExportJDE = sheets("Export to JDE")

shtHours.Select
  
'Next product and lot number and production line
   If Range("AX10") = "" Then
     MsgBox " Next Job Product Code must be entered.", vbOKOnly, "Ampacet Paperless"
    Exit Sub
       End If
    If Range("ax11") = "" Then
      MsgBox "Next Job Lot Number must be entered.", vbOKOnly, "Ampacet Paperless"
      Exit Sub
       End If
'end check

DateEnd = shtJCO.Range("G12")
TimeEnd = shtJCO.Range("G14")
DateTime = DateEnd & " " & TimeEnd
CurrPC = rngCurrPC.Value
CurrLOT = rngCurrLOT.Value
PLINE = rngPLINE.Value
NxtPC = rngNxtPC.Value
NxtLOT = rngNxtLOT.Value

'file name is differnt for the GA23 (Extrusion)
CURRfilename = shtJCO.Range("C4").Value & ("_operator workbook") & (".xlsm")
NXTfilename = shtJCO.Range("k18").Value & ("_operator workbook") & (".xlsm")
ThisWBook = CURRfilename

sheets("Export to JDE").Select
   
    Dim B As Long, BB As Long
    BB = Cells(Rows.Count, "O").End(xlUp).Row
    For B = BB To 1 Step -1
        If Cells(B, 1).Value = 0 Then
            Cells(B, 1).Offset(-1, 16).Select
        End If
    Next

Set DateFROMrng = ActiveCell
Set StartFROMrng = ActiveCell.Offset(0, 1)
Set EndFROMrng = ActiveCell.Offset(0, 2)
Set CodeFROMrng = ActiveCell.Offset(0, 3)
Set ReasonFROMrng = ActiveCell.Offset(0, 4)
Set CrewFROMrng = ActiveCell.Offset(0, 5)
Set IntFROMrng = ActiveCell.Offset(0, 6)
Set KeywordFROMrng = ActiveCell.Offset(0, 7)

Set rngDate = shtExportJDE.Range("P1", DateFROMrng)
Set rngStartTime = shtExportJDE.Range("Q1", StartFROMrng)
Set rngEndTime = shtExportJDE.Range("R1", EndFROMrng)
Set rngCode = shtExportJDE.Range("S1", CodeFROMrng)
Set rngReason = shtExportJDE.Range("T1", ReasonFROMrng)
Set rngCrew = shtExportJDE.Range("U1", CrewFROMrng)
Set rngINT = shtExportJDE.Range("T1", IntFROMrng)
Set rngKeyword = shtExportJDE.Range("V1", KeywordFROMrng)

Set shtJCO = Workbooks("TEMP06_Operator Workbook").sheets("JCO")
Set shtHours = Workbooks("TEMP06_Operator Workbook").sheets("Hours Sheet")
Set rngCurrPC = shtJCO.Range("G8")
Set rngCurrLOT = shtJCO.Range("C4")
Set rngPLINE = shtJCO.Range("G10")
Set rngNxtPC = shtJCO.Range("k17")
Set rngNxtLOT = shtJCO.Range("k18")
Set shtExportJDE = sheets("Export to JDE")

'add date and time to next workbook
Workbooks.Open "P:\Job Packets\" & rngNxtPC.Value & "\" & rngNxtLOT.Value & "\" & rngNxtLOT.Value & "_Operator Workbook" & ".xlsm"
Set NxtWBook = ActiveWorkbook
'NxtWBook = "P:\Job Packets\" & rngNxtPC.Value & "\" & rngNxtLOT.Value & "\" & rngNxtLOT.Value & "_Operator Workbook" & ".xlsm"

Set DateTOrng = Range("A19")
Set StartTOrng = Range("C19")
Set EndTOrng = Range("F19")
Set CodeTOrng = Range("I19")
Set ReasonTOrng = Range("K19")
Set CrewTOrng = Range("AO19")
Set IntTOrng = Range("AQ19")
Set KeyworkTOrng = Range("AT19")
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngDate.Copy
        Workbooks(NXTfilename).Activate
        DateTOrng.PasteSpecial xlPasteValues
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngStartTime.Copy
        Workbooks(NXTfilename).Activate
        StartTOrng.PasteSpecial xlPasteValues
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngEndTime.Copy
        Workbooks(NXTfilename).Activate
        EndTOrng.PasteSpecial xlPasteValues
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngCode.Copy
        Workbooks(NXTfilename).Activate
        CodeTOrng.PasteSpecial xlPasteValues
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngReason.Copy
        Workbooks(NXTfilename).Activate
        ReasonTOrng.PasteSpecial xlPasteValues
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngCrew.Copy
        Workbooks(NXTfilename).Activate
        CrewTOrng.PasteSpecial xlPasteValues
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngINT.Copy
        Workbooks(NXTfilename).Activate
        IntTOrng.PasteSpecial xlPasteValues
       
        Workbooks("TEMP06_Operator Workbook").Activate
        rngKeyword.Copy
        Workbooks(NXTfilename).Activate
        KeyworkTOrng.PasteSpecial xlPasteValues


Application.ScreenUpdating = True
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
In the section that does the copy/paste just stop the event that activates your userform:
add before the first line, like this:
VBA Code: 
Application.EnableEvents = False     '<- added
Workbooks("TEMP06_Operator Workbook").Activate
then at the end after the last line of the whole section, like this:
Code: 
KeyworkTOrng.PasteSpecial xlPasteValues
Application.EnableEvents = True     '<- added
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Run-time error 9, Subscript out of range - Copying sheet from closed workbook to active workbook as new sheet
Replies
9
Views
302
dvward
D
J
  • Question
Copying from one workbook to another
Replies
4
Views
430
josros60
J
K
  • Question
VBA Copy, Paste to Different Workbook, Save as then loop the action through slicer
Replies
1
Views
680
krisoey
K
gheyman
  • Solved
Copy from one workbook to another (just format and values)
Replies
2
Views
424
gheyman
gheyman
M
  • Question
VBA only copy data from one sheet that isn't already on another sheet
Replies
0
Views
491
mik1996
M

Forum statistics

Threads
1,215,077
Messages
6,122,991
Members
449,094
Latest member
masterms

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top