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
  'now copy from
  ThisWorkbook.Sheets("Members").Cells.Value = shtData.Cells.Value
  'Get Waiting Worksheet -----------------------------------------
  Set shtData = wbkMembershipData.Worksheets("Waiting")
  'clear internal worksheet
  '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
  '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

Open in new window

Any pointers would be most appreciated.
Who is Participating?

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

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.

Roy CoxGroup Finance ManagerCommented:
That code is trying to workl withh every cell in the sheet, hence the error. You need to work with a Range e.g ThisWorkbook.Sheets("Members").Cells(1 is A1 or say A1 to E10

With ThisWorkbook.Sheets("Members")
.Range(.Cells(1, 1), .Cells(10, 5)).Value
End With

Open in new window

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
RzzBAuthor Commented:
Oh - I see - I was expecting it to work similarly to doing it manually. If I select all cells manually and drag and drop them it does it in a trice without complaint. I did it this way because I wasn't sure how to select all data on a sheet when is not contiguous.

After a little research I found out about "UsedRange". So the code I'm using now is...

ThisWorkbook.Sheets("Members").Range(shtData.UsedRange.Address).Value = shtData.UsedRange.Value

Open in new window

Many thanks for your help.
Roy CoxGroup Finance ManagerCommented:
I would copy the data from the external sheets and paste to the corresponding sheets.  A little more info on what you are doing would help with a different approach - where are the external workbooks, what to copy etc. Maybe an example of the master workbook and sample of the data import
RzzBAuthor Commented:
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
Microsoft Excel

From novice to tech pro — start learning today.