PasteSpecial error text to number

Hi,

Stuck again, sample file should show my problem.

Open txt file and sample file to Sheet1, select data in txt file and use Insert button on Sheet1 to paste data.

Data copies as I require but formatting on columns F:H has errors? If I click error next to cell and convert to number it formats right.

How do I change code to make this work. I have tried time and time again with PasteSpecial and even looked in to calling a macro to delimit but no success.

Thanks

Can try this, red part maybe can be shorter but it works.

Please Login or Register  to view this content.

Kind regards

Add following 2 lines in bold just before End Sub.

Please Login or Register  to view this content.

Thanks both for your replies

#3 seems to be the easiest way to adapt my code and it works so thanks again. Just needed to add a formula to a particular cell so stop it being overwritten.

LeoTaxi I see I was probably not thinking of using the With command so thanks for the education, tried everything but I think lol

Will let you know later if it definitely works once I've tried it at work as that's where the problem always occurs

So... just do update.

Code I was using before this post was

Please Login or Register  to view this content.

I am pulling data from a work document and not sure of coding, when I ctrl C & ctrl V to notepad and then back to a blank excel sheet then copy from that sheet to my working sheet the data pastes as needed hence why my macro starts the way it does.

I am pasting in to blank sheet (Sheet1) to Range A1, when it works all works as it should. Clears cells in Sheet1 and kills Notepad.

Adding the two lines suggested in post #3 fixed the format issue

Please Login or Register  to view this content.

Still randomly getting an error when running the macro

Run-time error '1004':
Paste method of worksheet class failed

Please Login or Register  to view this content.

Debug shows code in red as the error

Not sure how I would put the suggestion in post #2 in to my macro as a whole, and not sure why I am getting this error