Trying to Loop This Code for Each Row Within the Range

[millikim]

Hello all,

I am trying to loop the following code for each row of my data set.

• r has data in A2:B100 of "ReplaceWith"
• FileName has data in D2:D100 of "ReplaceWith"

So I want it to

• select A2:B2 of r, take the FileName from D2 of FileName, Save.
• select A3:B3 of r, take the FileName from D3 of FileName, Save.
• select A4:B4 of r, take the FileName from D4 of FileName, Save.

...
And so on.

Any help would be greatly appreciated!

Please Login or Register  to view this content.

[Norie]

Perhaps.

Please Login or Register  to view this content.

[millikim]

Thank you so much Norie!
The above code works perfectly with a little tweak (for anyone who is facing the same problem...):

Please Login or Register  to view this content.

Thanks again!

[Norie]

Is this the tweak?

Please Login or Register  to view this content.

All that does is make 'KeywordList' will be the active worksheet when the new workbook is opened.

[millikim]

When I ran the macro without the tweak I got the following error:

"Run-time error '1004'
Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic."

Because it saved the tab "ReplaceWith" as a file, with the name taking the values in Column D, instead of saving "KeywordList." (So KeywordList tab still had the same name, leading to the error. It would have resulted in two tabs with the same name.)

The tweak selects "KeywordList" tab as the active tab, that tab gets saved, gets the value given in Column D (during which Excel re-names the tab), and at the end of the code it changes the tab name back to "KeywordList" so that the loop can repeat. This way no new workbook is opened, only text files are saved in the designated file location.

[Norie]

I don't follow, the code doesn't save a specific tab it saves a workbook and no sheet names are changed.

If you were getting that error then I should probably remove this line,

Please Login or Register  to view this content.

I only left it in because I wasn't sure what it's purpose was.

Now I've no idea what purpose, if any, it has.

[Norie]

Oops, just realised you are saving as text.

If that's the case I would suggest you copy the sheet to be saved to a new workbook and save that.

Then you won't be interfering with the structure of the original file.

To do that is straightforward.

Please Login or Register  to view this content.

[millikim]

It's because of this part:

Please Login or Register  to view this content.

The code saves as a text file, not a workbook. So it does only save one specific tab. (The initial code I put here did this for the first row within the range...)
So the tweaked code based on your code was exactly what I needed.

Thanks for your help!

[millikim]

Haha just read your reply
I actually only need text files, because the system I am trying to upload it to actually only accepts text files. So saving it as excel would have been just more files for me...

But that code works for me now! Thanks!

[Norie]

Do you mean the code to copy the sheet?

Did I get the right sheet?

[millikim]

I mean the tweaked code from #3. That is exactly what I need.

[Norie]

Did you try my suggestion of copying the worksheet to a new workbook?

If you used that you wouldn't need to be renaming any sheets.

Please Login or Register  to view this content.

[millikim]

It gets

"Run-time error '9':
Subscript out of range"

Also isn't this a trade-off between renaming a sheet vs. having a bunch of sheets? (i.e. if I have 100 rows I would end up with 100 sheets? or would it not do that? * revised: When I tried it, it created a separate workbook, which I do not want...)
I'm totally fine with renaming sheets because I don't want more than that one sheet. No extra tabs. No sheets. No workbooks.
But thank you for the many suggestions!

[Norie]

That's easy to fix, just close the newly created workbook/text file.

Please Login or Register  to view this content.

The reason I think it's a good idea to copy and save is because by just saving the workbook as text you are altering the structure of the workbook the code is in.

If the code is meant to generate a text file then it shouldn't do that.