CODEWITHSUNDEEP
vbaexcel
Mario
Mario

Reputation: 177

VBA EXCEL: Performing code on different workbook

I have a button which does the following:

Each sub is functional on its own but merging them together is giving me problems, probably because the i am trying to change a different workbook and the code is on the initial workbook.

Sub LoadData_Click()
    WPath = "K:\Chain\"
    WName = "import.xls"
    Workbooks.Open Filename:=WPath & WName
    With Workbooks(WName).Sheets(1)
        .Columns("A:H").UnMerge
        Call DataManager
        Call DateRegulator
    End With
End Sub

Sub DataManager()
    Dim Counter As Long
    Counter = Application.WorksheetFunction.CountA(Range("B:B"))
    Dim r As Integer
    For r = 1 And 5 To 8
        For K = 1 To Counter
            If IsEmpty(Cells(K, r)) Then
               Cells(K, r) = Cells(K - 1, r)
            End If
        Next K
    Next r
End Sub

Sub DateRegulator()
    Dim Counter As Long
    Counter = Application.WorksheetFunction.CountA(Range("B:B"))
    For K = 2 To Counter
        Cells(K, 2) = DateSerial(Year(Now), Month(Cells(K, 2)), 1)
    Next K
End Sub

Upvotes: 0

Views: 93

Answers (1)

Mike Woodhouse
Mike Woodhouse

Reputation: 52316

Your DataManager and DateRegulator subroutines don't explicitly specify which workbook/sheet they're working on. That's fine as long as you realise they'll work on whichever book & sheet are active when they're called.

So the first change I'd consider is 

With Workbooks(WName).Sheets(1)
    .Activate ' this makes sure the target workbook & worksheet are the active ones

Even better might be to pass the worksheet into the subroutines as a parameter.

There are several other potential issues in your code that you should address...

CountA() only counts non-Empty cells: if column B has any gaps, you won't process all the rows with non-empty values in the worksheet. If column B never has gaps then that's OK.

This line:

For r = 1 And 5 To 8

does not do what you think it does. That And doesn't let you create a disjoint sequence like [1, 5, 6, 7, 8], it's a boolean (True/False) operator, so it's like this:

For r = (1 And 5) To 8

...which evaluates to 

For r = 1 To 8

Assuming I've correctly guessed the intent, this would work:

Dim r As Variant
For Each r In Array(1, 5, 6, 7, 8)
    Debug.Print r ' replace with what you actually wanted to do here...
Next

Upvotes: 1

Related Questions