AutoFill method of range class error '1004' -excel 2003

[bucky33]

hello, using excel 2003
i'm using a macro that prompts the user for a *.txt file, then imports it into my excel starting in cell A7. my txt file is comma separated, like this; "123","456","abc" etc... 8 total fields.
in my excel worksheet, it puts the data in my columns fine except: two things after it runs -, i get an: AutoFill method of Range class failed and additionally, i need the import file to skip column G in my excel worksheet. I need that column G to be blank when the import has completed.

This is highlighted in the debug: Selection.AutoFill Destination:=Range("J7:V" & (NumRows + 2)), Type:=xlFillDefault
any help appreciated

[dreicer_Jarr]

Is the file going to be in the same directory all the time? Will it have the same name? I need more clarification when you say, "prompt the user for the file location. upon selecting the *.txt file".

Please advise and I'll see what I can do.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Import macros does not give a clear idea of the requirements. I would take it that you want to actually import a macro

[bucky33]

dreider_jarr,
i was a bit vague, i have update the post.

[bucky33]

RoyUk,
i have updated the post, hopefully not a vague, thks and sorry.
bucky33

[broro183]

hi Bucky33,

I'll add another two requests:
- Can you please edit your post to include Code tags (see the link in Roy's signature)?
- Can you please upload an example file which includes all your current code & is representative of your actual file?

Here are a couple of suggestions that may help, although it's hard to tell without seeing your file & existing code...

I need that column G to be blank when the import has completed.

Please Login or Register  to view this content.

This is highlighted in the debug: Selection.AutoFill Destination:=Range("J7:V" & (NumRows + 2)), Type:=xlFillDefault

What does the NumRows variable contain when you hold the mouse cursor over it while in debug mode?
Is NumRows declared as a Long?
(if not, I recommend changing it to Long.)

I'm not sure what range you're actually copying (we'll be able to see when you upload a sample file) but the below shows an alternative approach to the autofill method.

Please Login or Register  to view this content.

hth
Rob

[bucky33]

hi Bucky33,

I'll add another two requests:
- Can you please edit your post to include Code tags (see the link in Roy's signature)?
- Can you please upload an example file which includes all your current code & is representative of your actual file?

Here are a couple of suggestions that may help, although it's hard to tell without seeing your file & existing code...

Please Login or Register  to view this content.

What does the NumRows variable contain when you hold the mouse cursor over it while in debug mode?
Is NumRows declared as a Long?
(if not, I recommend changing it to Long.)

I'm not sure what range you're actually copying (we'll be able to see when you upload a sample file) but the below shows an alternative approach to the autofill method.

Please Login or Register  to view this content.

hth
Rob

Rob,
the NumRows varible is set to 4. attached is the code and the sample import file.
greatly appreciated

[broro183]

Hi Bucky33,

Now that I look at the problem line a bit more thoroughly (& have done some research) I think I've spotted two problems:
1) this is a guess... I think the destination range needs the same number of columns as the copied section (ie A:I = 9 columns whereas J:V = 13 columns).
2) The Excel Help Files state that the Destination is the

The cells to be filled. The destination must include the source range.

Once you adjust your copy & destination ranges to have the same number of columns, you could try the principles in my suggestion of post # 6.

Can you please edit your post to include Code tags (see the link in Roy's signature)?

The code tags belong around your line of problem code in your first post.

btw, If we are to help with an xls file, it is much more useful if you upload an xls file as the sample file. If you need more help (after changing the ranges), can you please upload an xls (Excel) file which has the same layout as your actual file?

hth
Rob