+ Reply to Thread
Results 1 to 4 of 4

Help Copying/transfering Data from one Workbook to ThisWorkbook

  1. #1
    Registered User
    Join Date
    11-14-2014
    Location
    Australia
    MS-Off Ver
    Office 2011 & 2010 (OSX & WIN7)
    Posts
    3

    Help Copying/transfering Data from one Workbook to ThisWorkbook

    Hi,

    I've just started trying VBA in excel and am trying to source financial information somewhat less painlessly than manual entry.

    I download the information in excel format and want to copy certain aspects of relevant workbook into a master workbook.

    When I run the VBA code, I get the following error: "Type mismatch error"

    Please Login or Register  to view this content.
    I tried altering the code to the one below simply copy and patse but get a "9 Subscript out of range error"

    Please Login or Register  to view this content.
    I'm not too sure where I'm going wrong and have looked at VBA code texts in my University Library and didn't get anywhere with it

    Any help is greatly appreciated!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,547

    Re: Help Copying/transfering Data from one Workbook to ThisWorkbook

    ASXCode is already a string value and does not need the double quotes.
    The Cells property requires two integers specifying the row and column numbers of a cell, for example: Cells(9,13)
    The Range property requires the name (a string value) of a cell. In this case: "A40"

    Thus: ThisWorkbook.Sheets(" & ASXCode & ").Cells("A40").Value = BalanceSheet
    should be:
    ThisWorkbook.Sheets(ASXCode).Range("A40").Value = BalanceSheet

    However(!) You delcared BalanceSheet as a String variable, but then you try to assign an array to it, which is an error. BalanceSheet must be declared as type Variant in order to contain a group of cells (array). Then, in order to write the data to the new location, you specify the location and size of the area to receive the data:

    Sheets(ASXCode).Range("A40").Resize(rowsize:=39, columnsize:=12).Value = BalanceSheet
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    11-14-2014
    Location
    Australia
    MS-Off Ver
    Office 2011 & 2010 (OSX & WIN7)
    Posts
    3

    Re: Help Copying/transfering Data from one Workbook to ThisWorkbook

    I'll try this tonight and reply how it goes

    Thanks for going through this protonLeah, very useful and much appreciated

  4. #4
    Registered User
    Join Date
    11-14-2014
    Location
    Australia
    MS-Off Ver
    Office 2011 & 2010 (OSX & WIN7)
    Posts
    3

    Re: Help Copying/transfering Data from one Workbook to ThisWorkbook

    Many thanks pL, managed to get this working based on what you had noted and a few modifications.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transfering data to another workbook
    By bimo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2013, 06:16 AM
  2. Copying VBA held in 'ThisWorkbook' to a new workbook
    By goodwinh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2011, 01:52 PM
  3. Transfering data to a different workbook automacticly
    By cat1410 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2008, 11:45 PM
  4. Transfering Data To another Workbook or Sheet
    By Crowbar via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2005, 11:05 AM
  5. Copying and transfering select rows of data
    By Parmenion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2005, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1