Question on VBA writing VBA

[MPW]

Hi All,

I am writing some VBA lines that exist in one workbook into another one.
To do this I first need to add and name the sheet names that exist in #1wkbk into #2wkbk. This I can do by using this:

    For Each ww In oldwkbk.Worksheets
        WSName = ww.Name
        If oldwkbk.Sheets(1).Name = WSName Then
            newwkbk.Sheets(1).Name = WSName
        ElseIf oldwkbk.Sheets(1).Name <> WSName Then
            newwkbk.Sheets.Add.Name = WSName
        End If
   Next

This will create new sheets in the new wkbk with the same names. My problem comes in with the sheet number.

Sheet1(Script)
Sheet6(Stats)

becomes

Sheet1(Script)
Sheet2(Stats)

This causes problems when transferring the lines of code. What I want to do is change the number of the sheet like I do when I name it using the routine above. That would also help me clean up the code by eliminating the "if" condition all together.

Thanks

 

[MPW]

Yes, you are right about Copy/Paste being the easier approach.

The reason I am duplicating the Sheets and the code instead of copying them is to try to eliminate the background baggage that Excel seems to hold onto.

I have workbooks that suddenly grow for no good reason. I have also run across some strange behavior like Ctrl+Home not going where it should. In this second example I had to go back to an older version to find one without the problem and then move forward from there.

As a general practice we don't even use saveas with the larger spreadsheets, instead we copy them in Explorer and then rename them. This seems to stopped some of the odd stuff, but some workbooks go back more than 10 years and were originally built in different versions of Excel.

I understand that copy/paste is probably not causing the problems but I thought it would be cleaner to totally start from scratch with a new workbook and build it with the all of the values from the original. I have worked out almost every type of cell and format and now I am working on the code side.

The up side of doing this is that I have learned a lot of things about how to use the tools. I owe a lot of thanks to this Forum for the help over the humps.

 

[AlphaFrog]

Save your old good workbook as a template, and then use that template to create new workbooks.

 

[MPW]

The workbooks especially one in particular have been revised with new content and/or code continually. If we ever finished revising it that would work.