Macro work in personal workbook, doesn't work in other workbooks

**Centexcel** · 09-30-2013, 10:23 AM

Excel Forum,

I have a piece of code that works in my personal workbook, but when I copy and paste it into the workbook that will use the macro I receive a run time error 1004.
I believe the error occurs after the new workbook is opened and is an object error.

If more context is needed, all I am trying to do with this macro is data entry from one sheet to another. I want to keep this macro in the sheet where I enter data. The workbook this macro opens changes each month.

Sub CCPIOPstmt()

Dim NewFN As Variant
Dim wbNew As Workbook

NewFN = Application.GetOpenFilename(Title:="Please select a file")
If NewFN = False Then
  ' They pressed Cancel
  MsgBox "Stopping because you did not select a file"
  Exit Sub
Else
  Set wbNew = Workbooks.Open(Filename:=NewFN)
End If
 wbNew.Activate

With Sheets("12 mo inc det")
        .Select
        .Unprotect
        Columns("A:B").EntireColumn.Hidden = False
            Range("ah12").Value = "1"
            Range("ah12").Copy
                Columns("a:a").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
            Range("ah13").Select

Thank you for your help. This forum is a great resource.

**Izandol** · 09-30-2013, 10:27 AM

On which line do you have the error?

**Centexcel** · 09-30-2013, 10:29 AM

I believe it is on the last line.
I don't get a debug message, I just receive a run-time error and the macro stops.

Thank you for your help.

**Solus Rankin** · 09-30-2013, 10:33 AM

Could you please include the code in its entirety and the error type?

**Centexcel** · 09-30-2013, 10:51 AM

CopyPasteMacro.xlsm

Attached is the code in its entirety.
The macro stops after wbNew opens but before column A is unhidden and multiplied by 1.
I receive a msg box "run time error 1004. Application defined or object defined error."

As I mention in my first post, the macro works great in my personal book, but fails when I paste it into another workbook.

Thank you for your time.

**Centexcel** · 09-30-2013, 11:10 AM

Any ideas?

**Solus Rankin** · 09-30-2013, 11:16 AM

I'm not exactly sure of the purpose of the macro, however on these lines here:

With Sheets("12 mo inc det")
        .Select
        .Unprotect
        Columns("A:B").EntireColumn.Hidden = False
            Range("ah12").Value = "1"
            Range("ah12").Copy
                Columns("a:a").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
            Range("ah13").Select

If all these are supposed to apply to Sheets("12 mo inc det") then they will need a . before each line. Otherwise they are going to try to apply to the active workbook. Adjust everything within you "With block" is going to be difficult. You can't just add . a the beginning of the line. You need it in very specific locations within the code. Any time an object refers to the sheet named in the with block it needs to be tied by to that sheet, such as:

With Sheets("12 mo inc det")
        .Select
        .Unprotect
        .Columns("A:B").EntireColumn.Hidden = False
        .Range("ah12").Value = "1"
        .Range("ah12").Copy
        .Columns("a:a").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
        .Application.CutCopyMode = False
        .Range("ah13").Select

However in some cases it will look like:

        Dim keyRange As Range, keyRow As Range
         Set keyRange = .Range("a1", Range("a65536").End(xlUp))
            Set keyRow = .Columns(1).Find(what:="4010", LookIn:=xlValues, lookat:=xlPart)
                If keyRow Is Nothing Then
                    MsgBox "not found"
                Else
                    keyRow = keyRow
                GPR = (.Cells(keyRow.Row, 14).Value)
                End If

Like I said, without knowing the purpose of the code its hard to find every object that needs to be tied to the "parent" sheet. I hope this makes sense.

**Centexcel** · 09-30-2013, 11:22 AM

Solus Rankin,

I think I understand.

With my macro in my parent sheet and with the parent sheet open, I am trying to open a workbook with financial data. Next I am trying to unprotect and reformat column A of the new workbook so I can look up key codes and return the values located in column 15. Next I am trying to paste the lookup values into my parent sheet.

Thank you for your time. I really appreciate it.

**Solus Rankin** · 09-30-2013, 11:47 AM

Try this: CopyPasteMacro.xlsm

I don't get any errors but I can't fully test it without the financial information sheets.

Macro work in personal workbook, doesn't work in other workbooks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Re: Macro work in personal workbook, doesn't work in other workbooks

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro Doesn't Work Through Button, Does Work Through Developer ->Macros Option

macro to copy cells to a new workbook doesn't work properly

[SOLVED] Event macro runs in wrong workbook/doesn't work properly

[SOLVED] Macro doesn't work when workbook is renamed

Macro that should create csvs of all the sheets of a workbook doesn't work! help!

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions