Hi,

I’ve the following code that works fine. It will update all the “brand” files that is located under the “brand” range in macro1.xls. The problem is recently I’ve added another name under the brand range in macro1.xls and I changed the following line in the vba code : from Do Until z = 20 to Do Until z = 21 and now the macro gives me the following error:

Run Time Error 1004 Method ‘Range’ of object ‘_Global’ failed

It will update all the “Brand” files except the new file that I created. What should I do?

any chance you can Zip and post the workbook you're using?

No. Sorry about that.

It could be because of using things like Activate for workbooks, or Select for worksheets. What you believe should be selected, after a line of code like:
ActiveWorkbook.Worksheets("Sheet2").Select

may not actually be what VBA has selected, so the following lines of code may not work as expected. Try changing it to properly qualify everything and see if it runs like that.

Here is an example:

I changed everything I possibly could, with the exception of a line or two where I myself can't tell which workbook/sheet you're referring to. (vba won't be able to, reliably, either).

Dump this in a standard module, note my comments, and try starting from this point, then see what errors you do/don't get.

Hi,

Thank you very much. But previuosly the code was working fine. It was when I added this new Brand it just don't update the new file. Its like its not finding the new file. When "Do z = 20" it don't gives me error but it won't look at the new file, but when I change "Do z = 21" it gives me error 1004 and it does not even touch my new file. Why is that?

John,

I understand that you feel that the original code worked fine, so the adjusted one should too; the problem is that with the use of things like Select and Activate, troubleshooting code becomes much more of a shot in a dark, a guessing game.

The way I look at this problem is that there are only two ways I would try to solve it that make sense:

1) debug the code (step through it by putting your cursor near the top of the Sub and pressing F8 - step through it line by line, when you get to the line that generates an error, check for the value of any relevant variables in the Immediate window. View>Immediate window, then test things by typing ?[variable name] and pressing Enter. or ?[myRange.Address] and hitting Enter. this is what I planned to help you do, if you were able to post the workbook. Without the workbook, you would need to do this yourself.

2) Re-write the code correctly, without depending on Select, Activate, etc. Then see if you still have the errror, if so, MUCH easier to troubleshoot.

The error of "range" method of "global" failing can be a lot of reasons.

Chief among them is simply the fact that VBA is telling you, "Range" unqualified with anything before it just isn't sufficient information to tell VBA what to do.

I recommend, try the code I posted and troubleshoot frmo that angle.

I tried your code and I'm getting the same error - 1004.

Range(brand).Copy is highlighted in yellow whenever I get this error, previously and now. It refers to Sheet2 in Workbook2.xls

Ok, leave the error bugging out and don't reset it.
This will be informative on how to debug things.

Go to View > Immediate window and type:
?brand and hit enter. What comes up?
Then try typing
?brand.Value What comes up?

In your code (and my revision, bothequally), I think the Dim statement isn't good. Try declaring them clearly on separate lines.
Dim brand as String
Dim city as String

?brand gives me my new brand name (the brand that I added)

?brand.Value gives me error '424': Object required

What's the brand name? for the sake of this, let's just say the brand name is ACME

Ok, so that tells me that when you use this:
Range(brand).Copy

You're literally telling vba:
Range(ACME).Copy Which won't make any sense to vba.
What's inside the parenthesis need to be a range object, or the string definition of a range object.

My suggestion at this point:

1) Declare brand, at the beginning, as a Range (not string) Dim brand as Range
2) change this line:

to be this instead:


3) finally, change Range(brand).Copy to brand.Copy