VBA Cpy and Paste is not working when i have another excel workbook open... Hi,

Question

VBA Cpy and Paste is not working when i have another excel workbook open...

Hi,

I have the following code in my vba program that activates when I hit an update button. The intention (and actual function) is to open another file, copy the data, and then paste it in my current file. It works fine, unless I have another excel file open. This can be any file, even brand new, unsaved workbook w/ no macros... any ideas? I've tried a few things, like more definitively naming the sheets

Sub UpdatePerpetualInventoryforMPS()

    AssignUniversalVariables

With ActiveSheet
    ChDir SalePath
    Workbooks.Open Filename:=SalePath & "" & SaleFile
    Sheets("Perpetual Inventory Ledger").Select
    Range("A5:AD10000").Select
    Selection.Copy
    Windows(MPSfile).Activate
    Sheets("Perpetual Inventory Paste").Select
    Range("A3").Select
    Sheets("Perpetual Inventory Paste").Unprotect

'HERE (Below) IS WHERE THE RUNTIME ERROR 1004 OCCURS

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Windows(SaleFile).Activate
    ActiveWindow.Close
    Windows(MPSfile).Activate
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
End With

End Sub

Explanation / Answer

For now I'd get rid of the

With ActiveSheet and its associated End With statement. They are just cluttering things up and I only see one line actually using the feature (the .Protect... statement).

Be even more specific, at least for the moment, where you have just Range() references; preceed them with the workbook/worksheet name as needed.

Is the file that this code is running in the MPSfile ? I don't see it 'defined' anywhere unless it's done during the AssignUniversalVariables process. If it is the same workbook that the code is running in, you could use ThisWorkbook.Activate instead of Windows(MPSfile).Activate

Here's how I might rewrite it using Objects and assuming I'm right about MPSfile being the workbook that the code is physically located in/running from:

Sub UpdatePerpetualInventoryforMPS_wObjects()
'some object variables we will use
'code also assumes that the code is running the MPSfile workbook
    Dim saleWB As Workbook
    Dim saleWS As Worksheet
    Dim destinationWS As Worksheet
   
    AssignUniversalVariables

   ChDir SalePath
   Set saleWB = Workbooks.Open(Filename:=SalePath & "" & SaleFile)
   Set saleWS = saleWB.Sheets("Perpetual Inventory Ledger")
   saleWS.Activate ' actually this is not needed & could be deleted! advantage of objects!
   saleWS.Range("A5:AD10000").Copy
   ThisWorkbook.Activate ' Windows(MPSfile).Activate
   Set destinationWS = ThisWorkbook.Worksheets("Perpetual Inventory Paste")
   destinationWS.Unprotect
'HERE (Below) IS WHERE THE RUNTIME ERROR 1004 OCCURS

'I removed all the parameters that were set to default anyhow

destinationWS.Range("A3").PasteSpecial Paste:=xlPasteValues
   'close the sale workbook without saving changes
   saleWS.Close False
   'Windows(SaleFile).Activate
   'ActiveWindow.Close
   'this shouldn't be necessary if only 2 files open, but if 3 or more...
   ThisWorkbook.Activate ' Windows(MPSfile).Activate
   destinationWS.Protect DrawingObjects:=True, Contents:=True, _
    Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
'now, good housekeeping: release assigned resources back to the system
Set saleWS = Nothing
Set saleWB = Nothing
Set destinationWS = Nothing
End Sub

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.