Applying Macro to Copied Over Worksheet from another Workbook

Question

Situation: Every month I need to take a source of data and re-format it so I can dump it into another file and update a pivot table. I want to automate the reformatting piece however I can't quite figure out the best way. Ideally, I would download the data source from online, copy the workbook to this Automated Workbook and run the macro. So I've recorded the Macro I need. See below for reference however now when I try to run to the copied over worksheet I get an "out of range" error. I'm guessing I need something that will allow me to run the Macro on this copied over sheet or all sheets of the workbook?

Current Code:

Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    ActiveSheet.ListObjects("Combined3").Range.AutoFilter Field:=6, Criteria1:= _
        "A_AS1001 - UCS"
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.ColumnWidth = 6.43
    Columns("M:N").Select
    Selection.Cut
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Columns("L:L").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Amount Ads"
    Range("P193").Select
    Columns("P:P").ColumnWidth = 17.71
End Sub

probably listobjects "combined3" doesn't exist in your new worksheet. Try typing code directly and avoid all the selects as it givesxerrors easily. — Lambik

Ahmed AU Ahmed AU · Accepted Answer · 2018-11-06T03:36:04

This is not an answer but an add on to catch & smoother the problem. The real problem is what @Lambik commented. However if you have no control over the downloaded data the code will give you some ways to tackle it. Add the code given just at the starting of Macro, it will check for existence of table "Combine3" and offer you some alternatives

Dim ListNames, Choice, InPrompt As String, Lst As ListObject, have As Boolean, Lcnt, Lno As Integer
 Choice = "Combined3"
 have = False

 'Check for listobjects in the worksheet
 Lcnt = ActiveSheet.ListObjects.Count
 If Lcnt = 0 Then
 InPrompt = " No table found " & vbCrLf & " Click Cancel to Quit " & vbCrLf & " Or enter 1000 to Add Current Selection as Combine3" & vbCrLf
 Else
 'Gather listobjects names
 For Lno = 1 To Lcnt
 ListNames = ListNames & Lno & ". " & ActiveSheet.ListObjects(Lno).Name & vbCrLf
    If ActiveSheet.ListObjects(Lno).Name = Choice Then
    have = True
    Exit For
    End If
 Next Lno
 InPrompt = "Choose the Table Number of the following tables found to Auto filter " & vbCrLf & ListNames & " Or Click Cancel to Quit " & vbCrLf & " Or else enter 1000 to Add Current Selection as Combine3" & vbCrLf
 End If

 If have = False Then
 Choice = InputBox(InPrompt)
    Lno = Val(Choice)
    If (Lno = 0 Or Lno > ActiveSheet.ListObjects.Count) And Lno <> 1000 Then
    Choice = ""
    Else
        If Lno = 1000 Then
        ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Combined3"
        Choice = "Combined3"
        MsgBox ActiveSheet.ListObjects(Choice).Range.Address & " added as table Combined3"
        Else
        Choice = ActiveSheet.ListObjects(Val(Choice)).Name
        End If

    End If
 End If


 If Choice = "" Then
 MsgBox " No valid choice made.Click ok to Exit"
 Exit Sub
 End If

 'For trial purpose only
 'Please delete the next two lines after trial
 MsgBox "Ok proceding for Auto Filtering" & Choice
 Exit Sub

Hope it will be useful

Applying Macro to Copied Over Worksheet from another Workbook

1 Answers