Run Time Error '1004': Select method of Range Class failed VBA

Hi everyone! I'm new in this community and I'm very glad to form part of it. First of all, I consider I am an intermediate user of VBA so I have a certain level of skill working with it. However, I am in trouble with this work I have to do because I don't get to accomplish my goal of running it.

First I will explain what my purpose is about.

I have a lot of workbooks, each with a code as a name, for example, one workbook is "2014T1AH54". where "2014" is the year "T1" is the first trimester (this could be T2 T3 or T4), "AH" is something that is not going to change and "54" represents a number which will vary from 1 to 180.

This workbooks have only one sheet (sheet 1) and each one contains information in tables which I have to extract of and place it in my workbook called "registro".

I have already achieved to extract the information from one workbook. However I get trouble when I have to extract of all these workbooks at the same time. The point is that the information in the table of each workbook must be placed if the table of my workbook "registro" is empty. In other words, I have to paste the content of the table going down. For example, once I copied the information of the table of 2014T1AH54 in the workbook registro table, then the next line of it will serve as the first place to enter the other table information of the workbook called 2014T2AH54, then going down I will copy the information of 2014T3AH54 and so on. I have my code here and this is:

Please Login or Register  to view this content.

What is highlighted with red color is the one that doesn't work. It doesn't work with select or activate and I don't know why. I don't get to run the program because I can't activate the range and proceed to find if activecell.offset(0,2) is empty. Because if this cell is full, then this means there is the information of the last workbook copied (for example: the information of 2014T2AH54). I can't copy if this cell or range is fulled, because, as well as I said, I want to copy all the information going down without loosing any info.

I hope I could be explicit in my purpose and hope answers please,

just in case, I have an example attached of for example, also I have the workbook called "datos2" where the code is located and I have the workbook called "2014T2AH54" where which I have to extract this information!

thank you very much in advance,

Jos

Hi Jos and welcome to the forum.

Your line of:
ThisWorkbook.Sheets(1).Range ("a5")
might need to be
ThisWorkbook.Sheets(1).Range ("a5").Select
instead.

It is much easier for us to help if you attach an Excel Workbook. You can do that by going to the "Go Advanced" and then clicking on the Paper Clip Icon above the advanced message box.

Or

move this line with continuation of the previous line of code (nearby the copy)

ThisWorkbook.Sheets(1).Range ("a5")

Welcome to the forum.

We'd like to help you but first..

Pls take some minutes to read forum rules and specially-in this case- rule#3 and add code tags around your code.

Also

Pls take some minutes to read forum rules and specially-in this case- rule#1 and amend your title

Then anyone will be able to help you.