Excel macro error

[jodyg64]

Hi, I'm trying to format data in an Excel file using a macro-enabled workbook and I'm getting an error 1004 The worksheet data for a table needs to be on the same sheet as the table. I'm not sure where it is the location is being changed in this code. Thanks so much!

Private Sub workbook_open()
    Dim src As Range
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    Workbooks.Open Filename:= _
        "C:\Merge Data Sources\callforsubmissions tab.xlsx"

    Set src = Range("A1").CurrentRegion
    Set ws = ActiveSheet
    ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, _
                      xlListObjectHasHeaders:=xlYes, _
                      tablestyleName:="TableStyleMedium28").Name = "Table1"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\jjones\McDow\Devo Team - General\Call for submissions data file\callforsubmissions tab.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Application.DisplayAlerts = True

End Sub

 

[Logit]

Your macro specifies "ActiveSheet" rather than a named sheet :

Set ws = ActiveSheet vs Set ws = Sheet1

Try changing ActiveSheet to the sheet name where TABLE1 is located.

 

[Alex Blakenburg]

I can't get your code to fail. Try fully specifying the references.

    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:= _
        "C:\Merge Data Sources\callforsubmissions tab.xlsx")
    Set ws = wb.ActiveSheet
    Set src = ws.Range("A1").CurrentRegion

 

[jodyg64]

Your macro specifies "ActiveSheet" rather than a named sheet :

Set ws = ActiveSheet vs Set ws = Sheet1

Try changing ActiveSheet to the sheet name where TABLE1 is located.

Thanks very much for your help. When the file (callforsubmissions tab.xlsx) is opened, it's the file name that becomes the name of the sheet (minus .xlsx) Do I set the worksheet to that name? If I do and put the name in quotes, I get a type mismatch error.

 

[Logit]

That was not the answer then. I was brainstorming ....

Did [B]Alex Blakenburg[/B]'s suggestion work for you ?

 

[jodyg64]

That was not the answer then. I was brainstorming ....

Did [B]Alex Blakenburg[/B]'s suggestion work for you ?

Thanks for your brainstorm! I thought I had tried Alex's suggestion, but I had missed the wb. before activesheet in the set ws=.wb.activesheet. Now that I caught that, it does work!! Thanks for your help!

 

[jodyg64]

I can't get your code to fail. Try fully specifying the references.

    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:= _
        "C:\Merge Data Sources\callforsubmissions tab.xlsx")
    Set ws = wb.ActiveSheet
    Set src = ws.Range("A1").CurrentRegion

Thank you very much, you made it work!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.