Copy Destination Syntax

I am attempting to create a macro that opens a file named "shrinkage-billing.xls", searches for a variable "PTOSH" in column A, copies the adjacent cell and pastes the data in another worksheet named "Shrinkage Report 2009.xls"

The code follows ...

Please Login or Register  to view this content.

I get the following error when I run this macro:

Run-time error '1004';
Method 'Range' of object'_Global' failed

I am not certain if I have the Offset syntax incorrect, or maybe the destination reference. As Shrinkage report 2009.xls is in fact the workbook that houses the macro, Can I use 'ThisWorkbook' instead of the actual filename? Also ... the data in Column B in "Shrinkage-Billing.xls" is in hh:mm format which does not paste well in excel. Any chance I can just copy the text before the : ??

Any help would be so appreciated, as somehow I have been dubbed the excel-meister of my workplace. It's a lie I tell you!

~M

If you're looking for the string PTOSH, you need to enclose it in quotes. There are other arguments to the Find method that default to the last used values; you should set them explicitly. And you had a syntax error in the Copy line unless the cell contained the string address of the range of interest.

Please Login or Register  to view this content.

My hero!!

Thank you so much for replying so quickly! Got the problem solved and today isn't even over yet.

Just one follow-up question ... the data in Column B in "Shrinkage-Billing.xls" is in hh:mm format which does not paste well in excel. Is there any way to just copy the part of the cell before the colon?

~M

Doesn't paste well?

There is no colon in the underlying data, it's just a number. You can format it as [h] if you just want to see whole hours.

I have attached a copy of the report and one of the underlying files. Please note the format of the "text" in row 'F' on the Calculation Tab in 'Shrinkage Report 2009.xls'. It cannot be formatted once pasted into even if the cell's format is already Number or HH:MM. As a result, the sum in row 'I' will not work. I figured the best way to combat this is to copy only the numbers before the colon in 'Shrinkage-Billing.xls' although that will skew the results ... just a paste special might work too.

Thanks so much!

~M

The problem is caused by the leading space in the cells in the source data file.
Ideally you would correct the Shrinkage-Billing.xls file so the times were actually times and not just text.

Unfortunately, that is not possible. Shrinkage-billing.xls is autorun from my workforce management software, I have attached a copy of the formatted excel option. I changed the macro to work with this report and ... oddly ... the result is the same.

I am now trying to figure out how to modify the macro to Trim 1 space off the left side and then format the cell [h]:mm? I think this will resolve all issues.

~M

There are several ways you could do this. Here's one.

Please Login or Register  to view this content.