1

I've created a button on one workbook that opens another macro-less workbook. transposeDataMatrices is the sub that will be run on the worksheets in the workbook that is opened:

Private Sub CommandButton21_Click()
Set BEEBook = ThisWorkbook

FileSelect = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", Title:="Please select the report to import")
If FileSelect = "False" Then Exit Sub
Set ReportBook = Workbooks.Open(FileSelect)

transposeDataMatrices

End Sub

Within transposeDataMatrices is the following, heavily truncated code (ReportBook is a global variable for the workbook being worked on in trasposeDataMatrices; cArray is a global array):

Public Sub transposeDataMatrices()

ReportBook.Activate

rowCounter = finWkst.UsedRange.Rows.Count
For ii = 1 To wsCount

    Worksheets(ii).Activate
    pullModelData (ii) ' just pulls some data, off ii worksheet in ReportBook

    ReDim indexIDArray(0 To 504) As Integer
    Dim j as Integer: j = 0
    For Each catName In cArray
        Dim totRange As Range
        Set catTitle = Worksheets(ii).UsedRange.Find(catName)

        If Not catTitle Is Nothing Then
        catTitle.Offset(2, 0).Offset(0, 1).Select
        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlDown)).Select
        Set totRange = Selection
        Else
        Set totRange = Cells("A1")
        EndIf
        indexIDArray(j) = j
    Next

    equipModelVersion rowCounter, indexIDArray
    rowCounter = finWkst.UsedRange.Rows.Count

Next ii

End Sub



Sub equipModelVersion(rowCounterDummy As Integer, indexArrayDummy() As String)

ReportBook.Activate
finWkst.Activate

iCol = 1:

    Set indexRange = Range(Cells(rowCounterDummy + 1, iCol), Cells(rowCounterDummy + 1 + UBound(valueArrayDummy, 1), iCol))

    Dim j As Integer: j = 0
    For I = rowCounterDummy + 1 To rowCounterDummy + 1 + UBound(valueArrayDummy, 1)
        Cells(I, iCol) = indexArrayDummy(j)
        j = j + 1
        Next

End Sub


Sub initializeWorkspace()
ReportBook.Activate
finWkst.Activate

Range("A1").Value = "IndexID"
Range("B1").Value = "ModelID"
Range("C1").Value = "UserVersion"
Range("D1").Value = "Equipment"
Range("E1").Value = "Date"

For ii = LBound(cArray) To UBound(cArray)
    Cells(1, ii + 5).Value = cArray(ii)
    Next

End Sub

I have two questions:

Firstly, when equipModelVersion gets run, it stores the values in the cells of the workbook on the worksheet where the button is located, rather than the workbook that is opened, in a sheet created to store the values.

How do I rectify that?

I tried activating that specific worksheet, using a with statement, and some other quick things I found on Stack Overflow, but nothing worked.

Secondly, when I was debugging the transposeDataMatrices and had it separately, the following line worked:

   Set totRange = Range(Range(Selection, Selection.End(xlToRight).Offset(0, -1)), Selection.End(xlDown))

In the macro that I transferred into the workbook with the button, it no longer works, so I had to work around it with: 

        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
        ReportBook.Worksheets(ii).Range(Selection, Selection.End(xlDown)).Select
        Set totRange = Selection

Just looks bad.

Why is VBA being so dumb about it, when the code is EXACTLY IDENTICAL, but expanded?

pnuts
  • 58,317
  • 11
  • 87
  • 139
George Terziev
  • 129
  • 1
  • 3
  • 14
  • 1
    get rid of all `.Activate` commands and just use `With` blocks to work with whichever objects you need (workbooks, sheets, etc.). Then use `.Range` or '.Cells` within the with blocks instead of just `Range` or `Cells` then it should work as needed. – Scott Holtzman Oct 09 '15 at 18:23
  • I tried With ReportBook.finWkst: .Range..., in the initiateWorkspace sub, but when the debugger gets to the first .Range, it throws up a compile error, and I am out of work-arounds so I chose to stick with the explicit way, in which case, it does not throw up a warning, but it also does not do it's intended function. – George Terziev Oct 09 '15 at 18:41
  • Never use Activate. The only valid purposes for Activate in VBA are 1) to show the user something and 2) you need to invoke a visual Excel method that can only work on the activated sheet and has no non-visual workaround (there are very few of these). – RBarryYoung Oct 09 '15 at 18:42

1 Answers1

6

VBA is not smart, it will only do what you tell it to do not what you think you told it to do.

My guess is that somewhere the ReportBook.Worksheets(ii) is losing focus.

It is good practice to always declare the parent when using objects. The easy way is to declare that parent object is as a variable.

in this case:

Dim ws as worksheet
set ws = ReportBook.Worksheets(ii)

then the with the two lines in question use a with statment

with ws
    Set totRange =.Range(Selection, Selection.End(xlDown))
end with

You should avoid using the .select command. See here for great information on that. Using it only dirties the code and makes it harder to find the errors.

Community
  • 1
  • 1
Scott Craner
  • 148,073
  • 10
  • 49
  • 81