Run-time Error Every Other Time The Macro Runs

JFrench · Oct 9th 2006

I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time.

Every time I get the error, it highlights this line of code:

Code

Sheets("Regenerate Request").Paste

This is all of the code up to where I get the error:

Code

Sub YesRegen()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email
    
    Application.Run "LogUnprotect"
    Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy
    'Selection.Copy
    Sheets("Regenerate Request").Activate
    Application.Run "RegenFormUnprotect"
    Range("A40:K40").Select
    Range("A40").Activate
    Sheets("Regenerate Request").Paste

Display More

Can someone help me?? Thanks in advance!!!

**Andy Pope** · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Is it possible the sheet is still protected when you do the Paste?

Dave Hawley · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

To stop the unprotecting/protecting of sheets for macros to work see: Run Macros On Protected Sheets

Also, wouldn't your ActiveCell differ each time?

JFrench · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Hi Andy---
I have actually put a line of code in that runs a seperate macro to unprotect the sheet after it is selected and before any cells are selected. The code for the RegenFormUnprotect macro that is called is

Code

Sub RegenFormUnprotect()


    Password = "ReGen"
    ActiveSheet.Unprotect Password


End Sub

Just to make sure this is not the error, I think I will unlock all the sheets and try the code, and see if that is it or not.

Hi Dave---
Thanks for the protecting/unprotecting info. I will try the UserInterfaceOnly method because that looks like its alot more effecient than what I'm currently using.

Yes, you are correct that the Active Cell will change each time. The user selects a cell in column A of the "Request Log" worksheet (the macro attached to the button on that sheet won't run unless they select a cell in column A). Since I have been having this problem with the run-time error, I have tried both using the same cell over and over, and trying different cells each time, and either way the macro only runs correctly every other time.

A co-worker of mine who knows a little about VBA suggested there might be something in the body of the macro that might be causing the problem. Would it be helpful if I posted the entire code? (I didn't before so as to keep my post short and concise)

**Andy Pope** · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

An example workbook would help.

**norie** · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Please post the entire code.

One way of avoiding the error might be to lose the activate/select parts of the code.

It's not generally needed to activate/select workbooks/ranges to work with them.

JFrench · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Hi Norie---
Here is the entire code. It is actually two macros; the first macro is attached to the button on the log page, and if the user hits Yes when the MsgBox pops up, the second macro is called. The purpose of this is to pull data out of out log and put it back onto the request form to send out for a bid on pricing. If the user hits No, the macro quits and the user is left on the log worksheet.

Code

Sub RegenerateRequest()
' finds active row and copies info into Re-request form to be sent to factory


    If ActiveCell.Column = 1 And ActiveCell.Row > 7 Then
    
' message box gives user option to proceed with or cancel re-generation request


        Dim cMsg, cStyle, cTitle
        cMsg = "Are you sure you would like to regenerate this request?" & Chr(13) & "Clicking Yes will post this request to the log." & Chr(13) & "Clicking No will cancel this action."
        cStyle = vbYesNo
        cTitle = "Confirm Regenerate Request"


        Resp = MsgBox(cMsg, cStyle, cTitle)
            Select Case Resp
            Case Is = vbYes
                Application.Run "YesRegen"
            Case Is = vbNo
                Exit Sub
            End Select
    
        Else: Resp = MsgBox(Prompt:="Please select a cell below cell A8 in the Request # column to regenerate", Buttons:=vbOKOnly)
    End If
    Application.Run "LogProtect"
End Sub

Display More

Code

Sub YesRegen()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email
    
    Application.Run "LogUnprotect"
    Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy
    'Selection.Copy
    Sheets("Regenerate Request").Activate
    Application.Run "RegenFormUnprotect"
    Range("A40:K40").Select
    Range("A40").Activate
    Sheets("Regenerate Request").Paste


' This sets all of the formulas in the Regenerated Sheet
    Sheets("Regenerate Request").Activate
    Range("C12").Select
    Selection.Formula = "=D40"
    Range("C14").Select
    Selection.Formula = "=E40"
    Range("C16").Select
    Selection.Formula = "=F40"
    Range("C18").Select
    Selection.Formula = "=G40"
    Range("C20").Select
    Selection.Formula = "=K40"
    Range("C22").Select
    Selection.Formula = "=H40"
    Range("C24").Select
    Selection.Formula = "=J40"
    Range("G5").Select
    Selection.Formula = "=G10+.01"
    Range("G10").Select
    Selection.Formula = "=A40"
    Range("G12").Select
    Selection.Formula = "=B40"
    Range("G14").Select
    Selection.Formula = "=C40"
    Range("G18").Select
    Selection.Formula = "=I40"
    
' this copies the new request # and date into A40 and B40
    Range("G5").Select
    Selection.Copy
    Range("A40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
' this selects row 40 from the Regen pg and inserts the info into the log as a new
' row then sorts the log by Request #
    Rows("40:40").Select
    Selection.Copy
    Sheets("Request Log").Select
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.Run "SortLogByRequestNumber"
    
    ' This copies the Regen Request Form into another file
    Sheets("Regenerate Request").Activate
    Cells.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("C10,C12,C14,C16,C18,C20,C22,C24,G5,G7,G10,G12,G14,G18").Select
    Selection.Locked = True
    'Range("G1").Value = Sheets("Regenerate Request").Range("C8") & "- " & Sheets("Regenerate Request").Range("C10") & " - Request # " & Sheets("Regenerate Request").Range("G6") & "- " & Sheets("Regenerate Request").Range("C12")
    'Selection.Value = "=CONCATENATE(C8," - ",C10," - Request # ",G6,"- ",C12)"
    
    
' Saves the new file with the text from G1
    
    Dim strFileName As String
 
    strFileName = ActiveSheet.Range("G1").Value
 
    strFileName = Application.GetSaveAsFilename(strFileName, "Microsoft Excel Workbook (*.xls),*.xls")
 
    ChDir "P:\DesignSheets\CostRequestLog\"
    ActiveWorkbook.SaveAs strFileName


    Application.Run "SendCostRequestEmail"


    ActiveWorkbook.Close savechanges:=False
    
    'This goes back to the main file and returns to the Request Log
    
    Workbooks("10-3 D05 Cost Request Book.xls").Activate
    Application.Run "RegenFormProtect"
    Sheets("Request Log").Select
    
End Sub

Display More

Hi Andy---
I am trying to get a sample file I can post...I just keep getting pulled away from it, and for some reason none of my passwords are working anymore so I have to figure that out first!

Thanks!!!!!!!

JFrench · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Hi Andy---
I finally got a sample workbook set up, but it is too big to post even when I have it zipped. Can I email it to you?

**norie** · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

What sheet is active when you run the code?

What sheet is active when you get the error?

Where are you copying/pasting from/to?

gmccreedy · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

omg its the attack of the evil excel macro recorder...
Have you tried just using the copy to destination feature?

Code

Sub YesRegen() 
     ' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
     ' the log, generates the new file and attaches it to an email
     
    Application.Run "LogUnprotect" 
    Application.Run "RegenFormUnprotect"
    Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy Sheets("Regenerate Request").Range("A40")

the more you select/activate things, the more the macro and efficiency slows down. you should never have to actually select anything. just call on it and execute your command.

Norie has mentioned this above. Try it...you wouldn't believe how much of that code (i say about 75%) is useless.

JFrench · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Hi Norie---
The active sheet when the RegenerateRequest macro is run is the Cost Request Log. The code copies over the cells in the ActiveCell.Row (the user selects the row) on the Log over to A40 on the Regenerate Request Form. When I get the error, the Regenerate Request Form is the active sheet.

Hi gmccreedy---
I'm still pretty new to VBA and am 100% self-taught through the help menu, what I've found here on OzGrid and information elsewhere on the web. I had seen the destination feature mentioned in one post, but couldn't figure out how to make it work with my code. I will try your suggestion and see if I can get it. I appreciate your advice to get rid of many of the selection and activation commands, and I did try to avoid those when I was writing this code, but I actually had to add some of those commands to get the code to where it is now. Is there anything specifically you would suggest that I take out, and if so, can you help me with the correct syntax to make the code run correctly? I've been struggling with this for awhile!!!

gmccreedy · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

will get back to you in a bit. Will trump it down for you...but I am sure these guys will trim it even further. Like you, I am still a newbie...but I can assure you this...you are in the right place for help!!!

Back in a bit.

JFrench · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Thanks!

gmccreedy · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Ok. I did a little damage to it. Tough to edit some of the code since I wasn't quite sure what it was doing. (you had a few areas where you pasted twice, so wasn't sure if it was a mistake and did the best I could to decipher.)

This is untested. I suggest not overwriting your original code and just set this in a new module. I renamed with a "UD" for updated.

Code

Option Explicit


Sub YesRegenUD()
     ' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
     ' the log, generates the new file and attaches it to an email
     
    Application.Run "LogUnprotect"
'***************************************
    Application.Run "RegenFormUnprotect"  ' moved this to top
'***************************************
    Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy Sheets("Regenerate Request").Range("A40")
'
     ' This sets all of the formulas in the Regenerated Sheet
With Sheets("Regenerate Request")
    Range("C12").Formula = "=D40"
    Range("C14").Formula = "=E40"
    Range("C16").Formula = "=F40"
    Range("C18").Formula = "=G40"
    Range("C20").Formula = "=K40"
    Range("C22").Formula = "=H40"
    Range("C24").Formula = "=J40"
    Range("G5").Formula = "=G10+.01"
    Range("G10").Formula = "=A40"
    Range("G12").Formula = "=B40"
    Range("G14").Formula = "=C40"
    Range("G18").Formula = "=I40"
     ' this copies the new request # and date into A40 and B40
    Range("G5").Copy
    Range("A40").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
'
    Range("G7").Copy
    Range("B40").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Rows("40:40").Copy
End With
     ' this selects row 40 from the Regen pg and inserts the info into the log as a new
     ' row then sorts the log by Request #
With Sheets("Request Log").Rows("10:10")
    .Insert Shift:=xlDown
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End With
    Application.Run "SortLogByRequestNumber"
     
     ' This copies the Regen Request Form into another file
    Sheets("Regenerate Request").Cells.Copy
    Workbooks.Add
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
'***************************************
' Not Sure why you are pasting twice...
'***************************************
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'    :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("C10,C12,C14,C16,C18,C20,C22,C24,G5,G7,G10,G12,G14,G18").Locked = True
    
    Dim strFileName As String
     
    strFileName = ActiveSheet.Range("G1").Value
     
    strFileName = Application.GetSaveAsFilename(strFileName, "Microsoft Excel Workbook (*.xls),*.xls")
     
    ChDir "P:\DesignSheets\CostRequestLog\"
    ActiveWorkbook.SaveAs strFileName
     
    Application.Run "SendCostRequestEmail"
     
    ActiveWorkbook.Close savechanges:=False
     
     'This goes back to the main file and returns to the Request Log
     
    Workbooks("10-3 D05 Cost Request Book.xls").Activate
    Application.Run "RegenFormProtect"
    Sheets("Request Log").Select
     
End Sub

Display More

I added a few comment cards with asterick lines for some areas.

JFrench · Oct 10th 2006

Re: Run-time Error Every Other Time The Macro Runs

Thanks so much for taking a crack at it! I knew there had to be easier ways to do alot of what I'm trying to do, just didn't know what they were. I will pop this into another module and see what it does

And as to your question about why I was pasting twice...Who knows!

I don't know if I'll be able to work on this today...I'm pretty busy, so it may be tomorrow or even Thursday before I get back to this, but I PROMISE I'll be back after I try this out!! (probably with more questions, but hey, why not...)

To everyone who has helped me out so far: [COLOR="DarkRed"]THANK YOU FOR YOUR HELP!!![/COLOR] Everyone I have encountered on OzGrid has been really wonderful, and I and many other newbies totally appreciate your help!

Dave Hawley · Oct 11th 2006

Re: Run-time Error Every Other Time The Macro Runs

JFrench, please don't ignore the rules you have agreed to and the text on the page you posted the new question from. Both clearly ask NOT to post any other questions in your Thread that do NOT match the Thread Title.

Run-time Error Every Other Time The Macro Runs

Participate now!

Share