Getting external worksheets into ThisWorksheet

Using Windows 7 Home (64bit) - Office 365 (32bit)

I have read many posts on this subject but they don't seem to fit my situation exactly.

I have a WorkBook running the code - it has three WorkSheets "Members", "Waiting" and "RenewalDate".  They are not contiguous - there are other worksheets.

I have an external WorkBook that also has three WorkSheets with the same names.

There must be a simple way of copying the data from the external worksheets to the local worksheets.

The code I have so far is as follows. the line "ThisWorkbook.Sheets("Members").Cells.Value = shtData.Cells.Value" causes an "Out of memory" error...

Sub GetMembershipData()

  Dim strExternalWBPathName As String
  Dim xapApp As Excel.Application
  Dim wbkMembershipData As Excel.Workbook
  Dim shtData As Excel.Worksheet
  
  'Build string and path of external workbook
  strExternalWBPathName = ThisWorkbook.Path & "\Data\CFAG Membership Data.xlsx"
  'Get a new Application object - because we want to hide the workbook
  Set xapApp = CreateObject("Excel.Application")
  'Hide the application and any workbooks
  xapApp.Visible = False
  'Open the workbook
  Set wbkMembershipData = xapApp.Workbooks.Open(strExternalWBPathName)
  
  'Get Members Worksheet -----------------------------------------
  Set shtData = wbkMembershipData.Worksheets("Members")
  'clear internal worksheet
  ThisWorkbook.Sheets("Members").Cells.ClearContents
  'now copy from
  ThisWorkbook.Sheets("Members").Cells.Value = shtData.Cells.Value
  
  'Get Waiting Worksheet -----------------------------------------
  Set shtData = wbkMembershipData.Worksheets("Waiting")
  'clear internal worksheet
  'ThisWorkbook.Sheets("Waiting").Cells.ClearContents
  'now copy from external worksheet to this worksheet
  ThisWorkbook.Sheets("Waiting").Cells.Value = shtData.Cells.Value
  
  'Get RenewalData Worksheet -----------------------------------------
  Set shtData = wbkMembershipData.Worksheets("RenewalData")
  'clear internal worksheet
  'ThisWorkbook.Sheets("RenewalData").Cells.ClearContents
  'now copy from external worksheet to this worksheet
  ThisWorkbook.Sheets("RenewalData").Cells.Value = shtData.Cells.Value
  
  Set shtData = Nothing
  
  wbkMembershipData.Close True    'Close and save the workbook
  Set wbkMembershipData = Nothing
  
  xapApp.Quit               'Close the instance of Excel we create
  Set xapApp = Nothing

End Sub

Select allOpen in new window

Any pointers would be most appreciated.
Roy