VBA adds unwanted filters and inserts an unwanted row?

Question

I have a vba code in ASSETS CALC workbook for selecting another workbook (ASSETS) through a user form. The code inserts a specific row in ASSETS, filters data, adds it together, and then copies the final number over to ASSETS CALC. Unfortunately, for some reason, the row that is inserted into the ASSETS workbook is also insterted into the ASSETS CALC workbook and filters are added. Does anyone know how to stop this? I just want the final number to be copied over to ASSET CALC.

Thank you.

    Dim str1 As String
Dim i As Integer

Application.ScreenUpdating = False

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        str1 = ListBox1.List(i)
    End If
Next i

    Workbooks(str1).Activate
    Sheets(1).Activate
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.AutoFilter
    ActiveSheet.Range("$A$4:$C$22").AutoFilter Field:=1, Criteria1:="=it*", _
        Operator:=xlAnd
    ActiveSheet.Range("$A$4:$C$22").AutoFilter Field:=2, Criteria1:="cash"
    Range("C28").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-11]C:R[-7]C)"
    Range("C28").Select
    Windows("Asset Calc..xlsm").Activate
    Range("D11").Select
    ActiveCell.FormulaR1C1 = "=[" & str1 & "]Sheet1!R28C3"
    Range("D12").Select

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Sep 30 '13 at 19:32

Automate This · Answer 1 · 2013-09-30T20:27:08.323

0

@Siddharth Rout provided a good link on referencing specific sheets and not relying upon 'ActiveCell' or 'ActiveSheets'. Make sure you read it.

Your problem at hand... I suspect your dealing with the whoa's of knowing exactly what is active at a given time. You don't have to active a workbook, sheet or range in order to get or write info to it.

To answer your first question, you can concatenate the string to use the link name like this:

ActiveCell.FormulaR1C1 = "=[" & str1 & "]Sheet1!R28C3"

To do this by defining your workbook instead of using 'Active' methods (on the formula side) try this:

Dim wb As Workbook
Set wb = Application.Workbooks(str)

ActiveCell.FormulaR1C1 = "=[" & wb.Name & "]Sheet1!R1C1"

I also noticed that you have an extra '.' in this line:

 Windows("Asset Calc..xlsm").Activate

Should read as

 Windows("Asset Calc.xlsm").Activate

edited Sep 30 '13 at 20:27

answered Sep 30 '13 at 20:19

Automate This

30,726
11
60
82

Thank you for the information. I updated the code and it works fine now except for one issue. The subtotal is not selecting the right cells. I want it to select C5 to C27. I recorded the subtotaling manually and inserted it into this code, but for some reason it is not calculating those cells. Any idea why? Thank you – user2770097 Oct 02 '13 at 13:56
1

Please mark as answered to your original question if your happy with it so the question can be closed. Thanks – Automate This Oct 02 '13 at 14:08
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-11]C:R[-7]C)" should read ActiveCell.FormulaR1C1 = "=SUM(R[-23]C:R[-1]C)" – Automate This Oct 02 '13 at 14:13
Or better yet, change it to: ActiveCell.Formula = "=Sum(C5:C27)" – Automate This Oct 02 '13 at 14:20

VBA adds unwanted filters and inserts an unwanted row?

1 Answers1