I have broken my macro

[cdhampshire]

Hi all, sorry to be a pain but I am trying to run a macro I have used a dozen times on a new workbook with different parameters and it keeps saying it needs debugging but having checked the code, the target and origin sheet names and ranges, I can not get it to work. a breakdown of what I am trying to do is

1. take a range of fIgures from a sheet called MOJFR
2. PASTE THEM INTO ROW b (OR THE NEXT AVAILABLE ROW) in another sheet called DEC
3. No overwrite of data when I hit submit.

The code I am using is;

Please Login or Register  to view this content.

Thanks all!!

[gmr4evr1]

You want to copy from MOJFR to DEC?
You need to change the sheet names accordingly
So, both Strategy Log sheets become DEC and Strategy becomes MOJFR

[cdhampshire]

Ahh Ive accidentally pasted an older version of the macro here instead of the one I am using. Here is the correct one;

Please Login or Register  to view this content.

Sorry!!

[cdhampshire]

AS A BIT OF AN UPDATE

When I run the debugger, it highlights the Sheets("MOJFR").Range("a38:v38").Copy row as yellow for some reason

Thanks

[xlnitwit]

What is the error message? If it's a subscript out of range error, it means the sheet name is wrong- perhaps having a leading or trailing space.

[cdhampshire]

I get a runtime error 40036

application-defined or object-defined error

I just double checked the sheet names and I have no spaces before or after im afraid

[jolivanes]

Don't use "Sub Copy()". (I assume that the "[/" is a copy/paste problem.)
"Copy" is, I think, a reserved name for vba. Something like "Copy_From_MOJFR_To_Dec" however is OK

If you have many sheets and you want to delete possible leading or trailing spaces, try this

Please Login or Register  to view this content.

To copy and paste, you could try this instead.

Please Login or Register  to view this content.

[cdhampshire]

Thank you for this. Im afraid I am still having the same run-time error as before

I have checked and it is a macro enabled workbook

[gmr4evr1]

@cdhampshire - I've tried your code in a mock-up workbook, and it ran with no errors, so I can't see why you're getting the error.

[xlnitwit]

Do you have any activex controls on that sheet, such as commandbuttons?

[mjr veverka]

What do you have selected\chosen in the "Tools\References" option in VBA Editor (ALT+F11) ?

Edit:
Some reasons for the error "40036" - maybe not in this case, but in general:
1. Corrupt download or incomplete installation of Microsoft Excel software
2. Damage in the Microsoft Excel system registry due to recent change of software related to the Microsoft Excel file (installation or uninstallation)
3. Virus or malware infection that has corrupted the Windows file or related Microsoft Excel files
4. Another program maliciously or mistakenly deleted Microsoft Excel-related files

[jolivanes]

Starting from the left, where are the sheets you copy from and copy into? Like the 2nd sheet and the 5th sheet or whatever