Transfer data between two workbooks.

[xpost][/xpost]

Hello everyone,

I have Sub TestTable in the file BacktestFile.xlsm that processes data of the single (active) sheet at a time and copies results to the added new sheet with the (same name) + T. The aim is to make it automatically loop through all sheets (except Sheet1) in the file BacktestFile.xlsm and add new sheets with results to workbook Results.xlsm with names identical to names in the source file (BacktestFile.xlsm). The sub works well within the same workbook, but I could not make it transfer data to another workbook. I run across problem of assigning name to the new sheet, it would always give error message that the sheet with this name is already there. I would be grateful for any help in solving this problem. Please find attached BacktestFile.xlsm with Sub TestTable, and file Results.xlsm with results as they should look like after data processed automatically (I added sheets with results to file Results.xlsm manually), as an attachment to this post.

Thanks in advance.

Dilshod

Quote from Carim

Hello,

This topic has already been addressed many times in the Forum ...

You have to determine if you are working with closed workbooks ... or are your workbooks always opened ...

Hello Carim,

Only one workbook and it is closed, but might be open as well, it does not matter as long as code will be working.

Thanks

Quote from royUK

Using ThisWorkbook will mean the code will work on the workbook that contains the code.

ActiveWorkBook means the code will run on the currently active workbook.

I'm not really sure what you want to do, but I think your code will be very slow.

Hello royUK,

Thanks for your message.

I'm not professional in coding, I'm physician. I can't make it work, not to mention about thinking about speed of the code.

Half of the code works within BacktestFile.xlsm as it has to copy list of the stocks from original sheet with table, except Sheet1 (sheet name in yyyy.mm.dd format), paste it to Sheet1 of the same workbook, then download stock prices from internet, sort data in descending order, then copy it and paste transposed to a new sheet in Results.xlsm - (from here code works within Results.xlsm), then insert alternating empty columns between columns with data, calculate change in percentage, then calculate Average Totals. The name of the new sheet in Results.xlsm should be the same as the name of the original sheet in BacktestFile.xlsm.

Dilshod

The thread has been cross published at

https://www.mrexcel.com/board/…ame.1158648/#post-5620914

Quote from royUK

57 posts and you have cross posted this without letting us know. You have no excuse for not having read the rules by now!!

mrexcel.com forum does not support workbooks attachments, the thread there was not intended to solve the whole problem, I just posted request to give me suggestions regarding other ways to give new sheet name the same as source sheet name, without using shname = Ativesheet.name formula. Later on I realized that this post will be left unattended because people (and apparently I was right), without workbooks attached, would not be able to understand what I want, that is why I decided to cross publish to ozgrid.com, and I did not inform initially because initial content of these two posts were not identical.

Quote from royUK

Do you need the time as well as the date?

No, time is not essential, I wanted to assign name the same as source sheet name in yyyy.mm.dd format.

Quote from Carim

Hello,

You will have to clarify your own process ... since coding with closed workbooks is not done in the same way as coding with opened workbooks ...

Hello,

Loop goes through multiple sheets of the file BacktestFile.xlsm and data copied to file Results.xlsm which is closed initially. I did not have problem with opening file Results.xlsm, problems started when I tried to add new sheets as it was giving error message that the sheet with that name already exists.

Thanks