Duplicating sheets and modifying them automatically?

ok I have an excel workbook with 1 sheet in it. I have to duplicate the sheet about 25 times, change 2 variables in the sheet and rename each sheet. I can do this by using move/ copy and changing the variables and sheet names manually.

My problem here is that i dont have 1 workbook, but 150 workbooks! so i have to do the copy-change variable-rename sheet about 3750 times :S

so is there any way i could get excel to duplicate a sheet 25times, change a predefined variable each time,and rename the sheets( with some predefined rule) ?? I know this sounds far fetched, but it would help a lot if this could be done...

anyone?

Yes, this is possible with a macro.

Please Login or Register  to view this content.

ok so the variables which change are unitweight (cell C1), fi (cell F2) and ru (cell F1). The naming convention= Gamma = ' the value in cell C1', phi= 'the value in cell F2', ru='the value in cell F1'.

im varying unitweight(C1) from 18 to 22 with a step of 2 (meaning ..18,20,22)
for each value of unitweight, Im varying the value of fi(cell F2) from 15 to 27 with a step of 2 again
and for each of these the value of ru.. 0, 0.2 and 10/C1

you can use 'slope3.xls' to show me know to do it.

thanks

Originally Posted by Whizbang

Yes, this is possible with a macro.

Please Login or Register  to view this content.

this bit returns an error saying 'object does not support this action'. can you help?

With Application.FileSearch
.LookIn = "The path to folder containing your sheets"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
End With

yes, this is it. I have entered the actual folder location instead of "The path to folder containing your sheets" the problem seems to be With Application.Filesearch. apparently it isnt available with newer versions of office. ( im running office 2010).. thanks!

Oh.. well, I can show you what it looks like in 2003. A 2010 expert will have to help you update it.

no problem

using the code from vbadud..

is sPath the location of the folder? and the following needs to be added?

sPath= "C\........"

Lovi, a good way to test something in the VB Editor is the Immediate Window. Go to View -> Immediate Window

Here you can type in code and have it immediately run. Type "msgbox(sPath)" to see what sPath's value is.

I haven't tested this, so... you may want to try it on test files first.

Please Login or Register  to view this content.

i tried it. at the moment its showing 'done' but without doing anything.

i changed *.xls to *.xlsx
replaced "master" with the sheet name

i did msgbox for activeworkbook.path.. that was ok

msgbox for sDir showed blank. is this normal? Shouldnt it be a list of files?

also, where is Sht used?

Oops. Delete the Sht. I copied this code from another project of mine and didn't clean up the Dim statments appropriately.

As for checking the value of sDir, I forgot to mention that you would need to be past the point where the variable is set. So, step into your project with F8, then keep pressing F8 until you pass the line that says "sDir = ". Then use your immediate window to check the value.

As for it not doing anything, that is my fault. I missed the \ before the "*.xls". If you use your immediate window and do msg(ActiveWorkbook.Path & "*.xls"), you will see the problem. ActiveWorkbook.Path does not provide the trailing slash.

yes now it accesses the worksheet and copies the sheet. gamma(2) is created.. but there is a little problem in changing the name. it does not recognise gamma(2) . i can see it on the worksheet but vb cant. any idea how to work around that?

also, could you check whether the 2003 code works please? im considering reverting to 2003.

yes now it accesses the worksheet and copies the sheet. gamma(2) is created.. but there is a little problem in changing the name. it does not recognise gamma(2) . i can see it on the worksheet but vb cant. any idea how to work around that?

also, could you check whether the 2003 code works please? im considering reverting to 2003.

In the code there is a space between "Master" and "(2)". Delete this space.

I'll test it tomorrow at work.