Error 438

[kevinu]

I had help with a code to past values from one sheet to another and it worked for a few days but now I'm getting an error 438. The interesting thing is the almost exact same code on the same page works. And a similar code on a different sheet works fine. I cant figure out why its happening. Ill attach the workbook. If you hit the "protocol" button at A1 on the Coherence page you get the 438 error. If you hit the "protocol" button on the phase page it works fine.


This is the code

Please Login or Register  to view this content.

It errors on the first "ws1.Cells(j, 22).PasteSpecial xlPasteValues" but if i delete that one and just have the second one it works fine... all that to say im very confused.

[mehmetcik]

Your Problem is Obvious to me.

Send me £100 and I will tell you. LOL.

No seriously.

If you had put ypur macro into a normal macro module it would have worked/

You didn't, the code is in a worksheet macro module.

A Worksheet Macro Module can only make changes in that worksheet.

[kevinu]

ok 2 questions then. why did it work for 2 days? and how do i move it to a module? I see i have 7 modules on the workbook which one do I put it on? Sorry if its a silly question, its all new to me. Ok 3 questions. If I just delete the first For statement the second one operates fine and its almost identical.

Thoroughly confused Kevin

[mehmetcik]

If you select the developer tab and then visual basic

Visual basic opens.


Select View and then Project Explorer

You will See a list of all your open workbooks.

Eg:

VBA Project(Book1)
Module1
Sheet1(Useful Code)
Sheet2(Sheet1)
ThisWorkbook

If you select insert, you can add Modules and Userforms.

If you double click on a sheet or module name that Macro module will open for editing.
If you double click on "This workbook" then the Workbook Macro Module will open.
Any macro here will work like a sheet specific macro module but will run on every sheet.



I had a fresh look at your workbook and changed the Workbook Macro to:-

Please Login or Register  to view this content.

[kevinu]

thanks for the code edit. I put the commandbutton_2 click code in the workbook module but it didnt change anything. I think I'm getting it wrong still. If I edit the button code to just do the 2nd For statement without the first it works fine. So is the issue where the code is? The code is pulling from 2 different columns and it seems the first one is the issue. Again, sorry for the noob questions. If its possible could you get the example sheet to work. Then I could copy the changes to the original.

[kevinu]

ok its fixed... but how i fixed it is stupid. I wrote the code again under the second For statement.. then erased the original... now it works ??????

[MarvinP]

Hi kevinu,

Read about scope of code at:

http://www.cpearson.com/excel/Scope.aspx
and
http://www.amarindaz.com/excel-vba-variables-scope/

[kevinu]

thanks for the resource

[Norie]

The only problem with the code in the workbook is a typo here,

Please Login or Register  to view this content.

where pastspecial should obviously be PasteSpecial.

Note, you can change other worksheets from within a worksheet module - you just need to reference everything properly.

[leelnich]

SERIOUSLY????!!!! kevinu, you FIXED the problem BEFORE pasting the code in your original post! I stared at that for 45 minutes, not wanting to download a wonky macro.

[kevinu]

Leelnich sorry the fix was totally an a shot in the dark. I just saw that the second For statement worked so I rewrote it with the ranges in the first one and rewrote a new second one and somehow it worked. In the original program the code was spelled correctly but in the example I must have messed it up. sorry for the error on my part. I also thought i was supposed to post in a new topic as it was a new problem. I thought i referenced it in that post. My bad