Run-time error '1004' Sorry we couldnt find.... file path

[caabdul]

I have set file location as

Please Login or Register  to view this content.

Following code works to reach to file1

Please Login or Register  to view this content.

But I want a cycle of code like following

Please Login or Register  to view this content.

Error (appearing on line Filename:=Nfile) says, we could not find file1.xlsx

I have tried (Filename:="file" & f) Or (Filename:=file & f) but get same error.

[JLGWhiz]

You could use an array like this.

Please Login or Register  to view this content.

But I would add .Value after the file1, file2 and file3 range assignments. eg. file1 = ThisWorkbook.Sheets("Main").Cells(8, 7).Value, Just to be on the safe side and prevent any compiler problems. You can only concatenate string values and in this case file1, while holding a string value is a code variable and not a real string.

[caabdul]

Hi JLGWhiz, Great it works

I have also added .vlaue

I still can't understand why my code was not working, seems file names loops don't work without arrays?

[JLGWhiz]

Hi JLGWhiz, Great it works

I have also added .vlaue

I still can't understand why my code was not working, seems file names loops don't work without arrays?

You probably missed what I added on about trying to concatenate non-string values. When you declare a variable like 'file1', the whole six digits is the variable name and when you initialize that variable, it becomes code, not a string. So you can't take part of it and concatenate the other part to it because VBA does not know what the two parts are when separated, even though you are trying to put them back together, it just won't work. They are not recognized by VBA as valid strings in the code.

[JLGWhiz]

You could have written the loop like this

Please Login or Register  to view this content.

I forgot to adjust the range in the last line of the loop before. It would have been +1 in the other post.

[caabdul]

Thanks for explaining. It makes sense

I am having a small trouble in reaching "Rw" with ThisWorkbook.Sheets("Rw")

I think the code goes to file1.xlsx to find "Rw" sheet instead of finding this sheet in my importing workbook which has name TotalRows.xlsx

Is there a way that I set ThisWorkbook to TotalRows.xlsx? so code searches TotalRows.xlsx fiel when I write ThisWorkbook.Sheets("Rw")

Thanks

[caabdul]

Sorry I was wrong. It does not look into file1.xlsx to find "Rw" sheet

instead there is some other issue. Error is Run time error 1004, Select method of range class failed.

[JLGWhiz]

Sorry I was wrong. It does not look into file1.xlsx to find "Rw" sheet

instead there is some other issue. Error is Run time error 1004, Select method of range class failed.

I don't see 'Select' in any of the code you posted but maybe you are trying to select something that is not a selectable object. When you click the 'Debug' button it should show you the line of code where the error occrred and if you can't solve it yourself you can post the relevant code so we can see it too.