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

 

[AlphaFrog]

    newwkbk.Sheets.Add Before:=newwkbk.Sheets(1), Count:=oldwkbk.Worksheets.Count
    For i = 1 To oldwkbk.Worksheets.Count
       newwkbk.Sheets(i).Name = oldwkbk.Worksheets(i).Name
    Next i

 

[MPW]

In this case:

Sheet1(Script)
Sheet6(Stats)

Sheets 2 through 5 no longer exist. Therefore my count won't go high enough. Is this correct?

After I wrote the first post I started to work with a "For i =" loop. I did not tie it to the count of sheets, instead I thought it might help me match the sheet name better, but to no avail.

If oldwkbk.Worksheets(i).Name = WSName then

Is there a way to reference the number of the worksheet and not just it's name?

 

[AlphaFrog]

Sorry. I don't really follow what you are asking.

 

[MPW]

Since I deleted some of the sheets from the oldwkbk I am left with a sheet6 even though I have only 2 sheets in the workbook. When I add new sheets to the newwkbk they are numbered consecutively sheet1, sheet2, sheet3, etc.. As a result even though my sheet has the correct name, it does not have the correct number.

newwkbk.Worksheets(Sheet2(Stats))

compared to

oldwkbk.Worksheets(Sheet6(Stats))

I need a way to change the number of the sheet that was created to match the sheet number from the oldwkbk.

 

[AlphaFrog]

Here's some example code to change a sheet's "Code Module Name"
http://www.exceltip.com/st/Change_t...e_names_using_VBA_in_Microsoft_Excel/406.html

You can also reference a sheet by an index number e.g. this references the 2nd sheet (by position) regardless of its' text name or code module name
Sheets(2)

 

[MPW]

I think what I need might be in the link that you sent, but I am having trouble adapting it for my uses.

I did not understand this part:

Properties("_CodeName")

I think if I could read what the sheet number is I could make it work.

As far as referencing the position of the worksheet, I am not sure that would help me in this application, but I would like to know the syntax of how to use it.

Thanks for the nudge.

 

[AlphaFrog]

This may help...
10 ways to reference Excel workbooks and sheets using VBA

I don't follow why you need to rename the sheet's code names. I've written a lot of macros and have never needed to do that.

 

[MPW]

Here is the piece of code that I have been working on.

    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    
    Dim VBProj2 As VBIDE.VBProject
    Dim VBComp2 As VBIDE.VBComponent
    Dim CodeMod2 As VBIDE.CodeModule
    Dim LineNum As Long
    
    Set VBProj = oldwkbk.VBProject
    For Each VBComp In VBProj.VBComponents
        Set CodeMod = VBComp.CodeModule
        Set VBProj2 = newwkbk.VBProject
        On Error Resume Next
        Set VBComp2 = VBProj2.VBComponents.Add(VBComp.Type)
        Set VBComp2 = VBProj2.VBComponents(VBComp.Name)
        VBComp2.Name = VBComp.Name
        If VBComp2 Is Nothing Then
            Set VBComp2 = VBProj2.VBComponents(VBComp.Name)
        End If
        On Error GoTo 0
        Set CodeMod2 = VBComp2.CodeModule
        For LineNum = 1 To CodeMod.CountOfLines
            CodeMod2.InsertLines LineNum, CodeMod.Lines(LineNum, 1)
        Next
        Set VBProj2 = Nothing
        Set VBComp2 = Nothing
        Set CodeMod2 = Nothing
    Next VBComp

This will (Once it is working properly) duplicate all the sheets, userforms, etc. and their code over to a brand new workbook. At the moment it will do every thing fine with the exception of if the sheet number does not match the original sheet number.

newwkbk.Worksheets(Sheet2(Stats))

compared to

oldwkbk.Worksheets(Sheet6(Stats))

I still need to create the userform controls and properties but that should not be hard once I get past this hurdle. If you can see a reason why it won't work other than the sheet number thing it would be great. Perhaps I'm missing something else. In the mean time I will check out the link that you sent me. Thanks for that.

 

[AlphaFrog]

This will (Once it is working properly) duplicate all the sheets, userforms, etc. and their code over to a brand new workbook.

Alternative suggestions:

It may be easier to SaveAs the old workbook. That will create a new copy of the original. You could then delete anything you don't want to keep in the copy.

You could copy the sheets from the old to a new workbook. Currently you are adding sheets to the new workbook and renaming them. If you copy the sheets from the original to the new, their Data, formats, and VBA code in the sheet's code module is copied as well.

This will copy all the sheets (and their code modules) to the new workbook

    For Each ww In oldwkbk.Worksheets
        ww.Copy After:=newwkbk.Sheets(newwkbk.Sheets.Count)
    Next