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?
 
NorieVBA ExpertCommented:
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
 
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
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.

All Courses

From novice to tech pro — start learning today.