+ Reply to Thread
Results 1 to 9 of 9

Question tying not to use .select

  1. #1
    Registered User
    Join Date
    10-06-2020
    Location
    West Midlands
    MS-Off Ver
    16
    Posts
    9

    Question tying not to use .select

    Hi guys,

    Have a question. trying to get VBA to create a table dynamically, as rows and columns may change in my data. Code I am trying: (Sheet_Anayser1_RS3_QC2 is sheet coded name)

    Sub TableFormatting_A1_QC2()

    lrow = Sheet_Anayser1_RS3_QC2.Range("A" & Rows.Count).End(xlUp).Row
    lcol = Sheet_Anayser1_RS3_QC2.Cells(1, Columns.Count).End(xlToLeft).column


    Sheet_Anayser1_RS3_QC2.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lrow, lcol)), , xlYes).Name = _
    "Tbl2"

    However, this only works if Sheet_Anayser1_RS3_QC2 sheet is the active sheet. Also gives run time error 1004 if I run this code and I'm not on the specified sheet.

    So the only way I can get this to work is to incorperate: Sheet_Anayser1_RS3_QC2.select

    Is there another way or what am I doing wrong? Please help!

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Question tying not to use .select

    .
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-06-2020
    Location
    West Midlands
    MS-Off Ver
    16
    Posts
    9

    Re: Question tying not to use .select

    Hi Logit,
    Thank you for your reply. But that still gives the same error.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Question tying not to use .select

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    10-06-2020
    Location
    West Midlands
    MS-Off Ver
    16
    Posts
    9

    Re: Question tying not to use .select

    Hi shg,
    Thank you for your reply. But that doesnt work it doesnt switch sheets

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Question tying not to use .select

    That was the point, to not have to select the sheet, no?

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Question tying not to use .select

    .

    Please Login or Register  to view this content.
    The above is as direct and specific as you can code it.

    If the above does not reference the sheet and range/s you are requiring, you will need to post all of your code so we can understand what else is
    occurring within your macro.

    Thanks.

  8. #8
    Registered User
    Join Date
    10-06-2020
    Location
    West Midlands
    MS-Off Ver
    16
    Posts
    9

    Re: Question tying not to use .select

    Thank you Logit. I'm not sure if I can code without using .select & . activesheet.

    Entire working code below (.select & .activesheet) below:

    Sub TableFormatting_A1_QC1()

    lrow = Sheet_Anayser1_RS3_QC1.Cells(Rows.Count, 1).End(xlUp).Row
    lcol = Sheet_Anayser1_RS3_QC1.Cells(1, Columns.Count).End(xlToLeft).column

    Sheet_Anayser1_RS3_QC1.Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lrow, lcol)), , xlYes).Name = "Tbl2"

    End Sub

  9. #9
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Question tying not to use .select

    .
    If this sheet : Sheet_Anayser1_RS3_QC1 is already in view, and is the sheet that currently has the focus,
    you should be able to use ACTIVESHEET rather than the sheet name.

    You can also do away with using Sheet_Anayser1_RS3_QC1.Select because that sheet is already in focus. No need to tell Excel "I'm using this sheet
    but just in case, I'm going to SELECT it anyways. Hope you don't mind."

    Again, if our suggestions for coding do not work, you really need to post all of your code because something else is going on that we can't see yet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. tying picture names into cells
    By 9d1awd in forum Excel General
    Replies: 1
    Last Post: 06-16-2014, 11:00 AM
  2. Tying a IF statement to an entire column
    By slufishman in forum Excel General
    Replies: 4
    Last Post: 07-18-2013, 03:29 AM
  3. [SOLVED] Tying two cells together so they are MUTUALLY dependent
    By lesoies in forum Excel General
    Replies: 11
    Last Post: 09-10-2012, 01:16 PM
  4. Tying TRUE or FALSE Formulas Together
    By artiststevens in forum Excel General
    Replies: 2
    Last Post: 12-14-2010, 02:59 AM
  5. Excel 2007 : help tying cells together
    By mswain1 in forum Excel General
    Replies: 3
    Last Post: 05-20-2009, 03:39 PM
  6. Tying Two Documents Together
    By swiftbmx in forum Excel General
    Replies: 1
    Last Post: 04-03-2007, 11:54 AM
  7. Tying an imported graphic to a cell
    By T.R. Young in forum Excel General
    Replies: 0
    Last Post: 10-05-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1