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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

I
  • Question Question
Value not shown in ComboBox1 when code run
Replies
1
Views
53
ipbr21054
I
I
  • Solved
Subscript out of range when copying values to another sheet
Replies
9
Views
187
ipbr21054
I
M
  • Solved
I can't get copy from one worksheet to another to work
Replies
4
Views
304
MARK858
MARK858
P
  • Question Question
Copy a worksheet from one workbook to another workbook based on conditional statements
Replies
5
Views
361
HighAndWilder
H
I
  • Question Question
VBA code to Copy values to another workbook
Replies
1
Views
389
Anthony47
A

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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