• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 86
  • Last Modified:

VBA modification help needed from earlier solution (VBA to modification to take it from table instead of sheet)

I had this question after viewing VBA to modification to take it from table instead of sheet.

How can i modify the code in my earlier question, that if  the source worksheet has a table then take data from table
    'Copy Data From Source Workbook
    Set SourceWs = SourceWB.Sheets(1)
    Set CopyRng = SourceWs.ListObjects(1).Range  

Open in new window


If sheets(1) does not have a table then use the code below

   Set SourceWs = SourceWB.Sheets(1)
    SourceLR = SourceWs.Range("A2").SpecialCells(xlCellTypeLastCell).Row
    SourceLC = SourceWs.Range("A2").SpecialCells(xlCellTypeLastCell).Column
    Set CopyRng = SourceWs.Range(SourceWs.Range("A2"), SourceWs.Cells(SourceLR, SourceLC))

Open in new window

0
Flora
Asked:
Flora
  • 3
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try
    Set lo = Nothing
    Set SourceWs = SourceWB.Sheets(1)
    On Error Resume Next
    Set lo = SourceWs.ListObjects(1)
    On Error GoTo 0
    If Not lo Is Nothing Then
        Set CopyRng = lo.Range
    Else
        SourceLR = SourceWs.Range("A2").SpecialCells(xlCellTypeLastCell).Row
        SourceLC = SourceWs.Range("A2").SpecialCells(xlCellTypeLastCell).Column
        Set CopyRng = SourceWs.Range(SourceWs.Range("A2"), SourceWs.Cells(SourceLR, SourceLC))
    End If

Open in new window

Regards
1
 
NorieVBA ExpertCommented:
Here's a slightly different version.
    Set SourceWs = SourceWB.Sheets(1)

    With SourceWs

        If .ListObjects.Count = 0 Then
            SourceLR = .Range("A2").SpecialCells(xlCellTypeLastCell).Row
            SourceLC = .Range("A2").SpecialCells(xlCellTypeLastCell).Column
            Set CopyRng = .Range(.Range("A2"), .Cells(SourceLR, SourceLC))
        Else
            Set CopyRng = .ListObjects(1).Range
        End If

    End With

Open in new window

1
 
FloraAuthor Commented:
Thank you very much Rgonzo1971 and Norie.

very much appreciated!    you guys saved me a lot of time.   it is really magical. i am so happy!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FloraAuthor Commented:
Dear Norie and Rgonzo1971

somehow the ListObject(1).Range does not work. i posted a new question here https://www.experts-exchange.com/questions/29070714/VBA-Import-frm-TBL-does-not-work-earlier-helped-by-Norie-and-Rgonzo1971.html
0
 
NorieVBA ExpertCommented:
Flora


How does it not work?
0
 
FloraAuthor Commented:
Thanks Norie.

it works now. it did not work because there was a hidden sheet.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now