Calling another macro while cycling through worksheets

[ADTIBE]

Hi,

I'm trying to cycle through the sheets in a workbook. In each cycle, I run some copy paste code onto a template and save that template with a name from a specific cell in the cycling workbook's active sheet. The problem is that while the saving code cycles and saves file names correctly, my ws.Application.Run ("CopyDTDvalues") does not cycle to copy paste correctly. It copy pastes the 1st sheet's values over and over. Can I have some advice on how to make sure the copy/paste code also cycles through the worksheets? Thanks!

Here is the code for my overarching code that cycles and calls another macro:

Sub Transfer_All_DTD_Inputs()
    Dim ws As Worksheet
    Dim ThisFile
    Dim NameToBeSaved
        For Each ws In ActiveWorkbook.Worksheets
            ws.Application.Run ("CopyDTDvalues")
            ThisFile = ws.Range("A2").Value
            NameToBeSaved = ThisFile & "_dtd"
            ActiveWorkbook.SaveAs Filename:=NameToBeSaved
            Workbooks(NameToBeSaved & ".xls").Close SaveChanges:=False
            Workbooks("DTD Bloomberg API template_v4.xlsm").Activate
            On Error Resume Next 'Will continue if an error results
        Next ws
End Sub

Here is some sample code from my copy/paste code.

Sub CopyDTDvalues()
            Application.Workbooks.Open ("C:\...blanktemplate_dtd.xls")
            Windows("DTD Bloomberg API template_v4.xlsm").Activate
            Range("B5:C5").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Windows("blanktemplate_dtd.xls").Activate
            Range("B2").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
End Sub

 

[Engineer Joe]

whenever you type "Range" and you don't preference it with it's parent (the worksheet), then it tries to use the activeworksheet. if i were you, i'd open the vb editor, click on the beginning sub and hit F8 which will start stepping you through the code. As you step through, you can see the variable and range values by either adding a watch to them or by running your cursor over them after they have been assigned. This will help you identify what's going on and how to fix it. It will also remind you that it's a good idea to either assign a range to a variable early on or explicitly state which worksheet and workbook the range you are referring to belongs (ended sentence in a preposition if this parenthetical observation wasn't here.) One thing you'll notice if you step through the code is that if you stop and select on different workbooks halfway though the code execution, then I believe it can change what the default parent values of "Range" when that is the only way you define it. Make sense?

 

[ADTIBE]

Thanks for the tip.

 

[Engineer Joe]

btw, you may be able to fix it just by adding ws.activate right after you start your "For...each" statement. This would make that worksheet the active one....