VBA to modification to take it from table instead of sheet

Hello,

i have this code below.

how can i modify this code. so instead of the lines below in takes it from Sheet(1) Table.  Sheet(1) only has one table. i want to copy all data from that table in source workbook into Target workbook.

thanks.

    'Copy Data From Source Workbook
    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



Sub UploadQuery()
Dim TargetWs As Worksheet, SourceWs As Worksheet
Dim TargetLR As Long, TargetLC As Long, SourceLR As Long, SourceLC As Long
Dim FolderPath As String, Filter As String, Caption As String, SourceFName As Variant
Dim SourceWB As Workbook, TargetWB As Workbook
Dim ClearRng As Range, CopyRng As Range

FolderPath = Application.ThisWorkbook.Path
ChDir FolderPath
Filter = "Excel files (*.xl*),*.xl*"
Caption = "Please Browse & Select the downloaded File "
SourceFName = Application.GetOpenFilename(Filter, , Caption)

If SourceFName = False Then
    MsgBox "You have CANCELLED selection of needed FILE", vbCritical, "- FOLLOW INSTRUCTION"
    Exit Sub
Else

Set SourceWB = Application.Workbooks.Open(SourceFName, Format:=xlDelimited, Local:=True)

    'Disable Events
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = True
        .StatusBar = "!!! Please Be Patient...Updating Records !!!"
        .EnableEvents = False
        .Calculation = xlManual
    End With
    
    'Clear Old Data
    Set TargetWB = Application.ThisWorkbook
    Set TargetWs = TargetWB.Worksheets("MySHEET")
    TargetLR = TargetWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    TargetLC = TargetWs.Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Set ClearRng = TargetWs.Range(TargetWs.Range("A1"), TargetWs.Cells(TargetLR, TargetLC))
    
    If Not IsEmpty(ClearRng) = True Then
        ClearRng.ClearContents
    End If
    
    'Copy Data From Source Workbook
    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))
    
    'Patient Number
    CopyRng.Copy
    TargetWs.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    ' Close Source Workbook
    Application.DisplayAlerts = False
    SourceWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    TargetWs.Activate
    TargetWs.Columns.AutoFit
    TargetWs.Range("A4").Select
    
    
    'Enable Events
    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .StatusBar = False
        .EnableEvents = True
        .Calculation = xlAutomatic
    End With
    MsgBox "!!! File Import Is Completed Now !!!"
End If
End Sub

Open in new window

LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Try something like this.
    'Copy Data From Source Workbook
    Set SourceWs = SourceWB.Sheets(1)
    Set CopyRng = SourceWs.ListObjects(1).Range

Open in new window

Note, this will include the header row but can easily be adjusted to copy only the data by using DataBodyRange.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
then try
    Set CopyRng = SourceWs.Range("NameOfYourTable")

Open in new window

Regards
0
FloraAuthor Commented:
thanks very much. you guys are my heros.
0
FloraAuthor Commented:
i have posted another question for a modification which i could not solve by myself.

here is the question link https://www.experts-exchange.com/questions/29068174/VBA-modification-help-needed-from-Norie-and-Rgonzo-earlier-solution.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.