+ Reply to Thread
Results 1 to 16 of 16

how to copy column data from one sheet to another

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    how to copy column data from one sheet to another

    Hi,

    I have a master sheet "hist" which has item ID, dataID,Type

    and in workbook "Sam.xlsx" i have the same Item id's in column D

    With column D(item ID) as refernce in sam.xlsx & i tried to bring dataID from "hist" doin a vlookup.

    But this loops in hist only til the "D"row count of sam.xlsx I want it to check til the last row of hist sheet and apply the vlookup formula in sam.xlsx

    but hist is updated each time, the row count increases.
    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    01-01-2013
    Location
    melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    55

    Re: how to copy column data from one sheet to another

    dont vlookup against the range but against the whole column?

    Please Login or Register  to view this content.

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: how to copy column data from one sheet to another

    Hi, amethystfeb,

    yopu woulkd need to fully qualify the range with the sheet name otherwise the range would be suggested to be on the active sheet by VBA.

    Instead of
    Please Login or Register  to view this content.
    try
    Please Login or Register  to view this content.
    If the sheet is located in a different workbook you would need to qualify the workbook name including extension as well if it is open.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: how to copy column data from one sheet to another

    Hi,

    I get "Subscript out of range" error
    Please Login or Register  to view this content.
    hist.xlsx is another workbook saved in some path this is static and never change.

    I have attached 2 workbooks
    in sam.xlsx in cell N2 i write a vlookup to get ID column data equivalent to inst id in column D. Comparing with the inst id in hist.xlsx
    Hist.xlsx is updated each time and row count incerases so I need to go till the last row in hist.xlsx and equivalent data
    Attached Files Attached Files

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: how to copy column data from one sheet to another

    Hi, amethystfeb,

    whom are you referring to?

    Please Login or Register  to view this content.
    Workbook hist.xlsx was open when I ran the macro.

    Ciao,
    Holger
    Last edited by HaHoBe; 03-01-2014 at 09:20 AM.

  6. #6
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: how to copy column data from one sheet to another

    Thanks,it works fine

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: how to copy column data from one sheet to another

    Hi, amethystfeb,

    if that took care of your problem please mind to mark this thread as Solved via Thread Tools over the opening post. Thank you.

    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: how to copy column data from one sheet to another

    Hi Holger,

    Could pls suggest me for this code to work if the sheet(hist.xlsx) is not open.

    That it should jus refer to the workbook with this name.

    When the workbook hist.xlsx is closed it prompts to open the workbook. i don want that, I jus want it to be referred with name"hist.xlsx"... I am not sure of the syntax to get this done.
    Last edited by amethystfeb; 03-02-2014 at 12:27 AM.

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: how to copy column data from one sheet to another

    Hi, amethystfeb,

    you would need to add the qualified path to the workbook as well. I changed the code to work for full columns and assumed both workbooks to be located in the same directory:
    Please Login or Register  to view this content.
    If it it a different directory you should adapt the drive and folder and use a code line like
    Please Login or Register  to view this content.
    Ciao,
    Holger

  10. #10
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: how to copy column data from one sheet to another

    I get subscript out of range error

  11. #11
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: how to copy column data from one sheet to another

    Hi, amethystfeb,

    if itīs Error 9 you would need to check the drive and folder for the workbook as well as the name as VBA canīt find the file you want to get the information from.

    Ciao,
    Holger

  12. #12
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: how to copy column data from one sheet to another

    Hi,

    The code provided first does nt work now even when hist.xlsx is open, it prompts to open the file. I am nt sure why

    Now i did a record macro and the when the workbook is not open below is wat is generated

    Range("N2").Formula "=VLOOKUP(D2,'D:\LH_Karthick\[HIST.xlsx]MasterSheet'!A:B,2,FALSE)"

    But the path"D:\LH....." will change, instead how do i refer it to path specified in cell A2(where i ve saved this hist.xlsx) of active workbook

  13. #13
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: how to copy column data from one sheet to another

    Hi, amethystfeb,

    Please Login or Register  to view this content.
    You would need to adapt the code line if your cell holds a backslash as the last character.

    Ciao,
    Holger

  14. #14
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: how to copy column data from one sheet to another

    Hi Holger,

    Thanks a lot... this works perfectly fine....

    And one more question when i open hist.xlsx to update a new row in it throws a pop up that it has a reference.

    Can i get rid of that pls. Is it possible

  15. #15
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: how to copy column data from one sheet to another

    Hi, amethystfeb,

    does that have anything to do with the original posted question?

    The normal/natural way of solving this problem would be to open up the workbook hist, set the values, close down hist. You can try setting the alarms on notification to false in the options which may not work but would definitely work via VBA when a workbook is opened from another one.

    And thatīs my very last answer in this thread.

    Holger

  16. #16
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: how to copy column data from one sheet to another

    Thanks alot Holger.. it really helped...

+ 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. how to copy columns of another sheet with the column data of current sheet
    By amethystfeb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2014, 11:28 PM
  2. Replies: 2
    Last Post: 01-22-2014, 05:36 PM
  3. Macro button to copy data from one sheet to another sheet's next available column
    By Alice21 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-23-2013, 08:02 AM
  4. Copy Data from one column in a sheet to another column present in 2nd sheet
    By pan07 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-21-2012, 12:50 AM
  5. Copy column data on one sheet to row or column date on another sheet based on user in
    By soready42012 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2012, 12:03 AM

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