+ Reply to Thread
Results 1 to 16 of 16

Transfer Data to another sheet

  1. #1
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Transfer Data to another sheet

    Hello all!!! Good Evening!!

    I have two sheets "INPUT" and "TABLE".

    In the Input Sheet I have a Data Feeding Table which has a component called Serial Code, where I give the Serial Code then matching product name appears in row below (used Index/match formula). There after I input manually all the rows below i.e, Var 1 to Var 20 for that serial code.

    What I want is I want all that values go and sit in TABLE sheet against the corresponding serial code in their respective columns when I execute a macro.

    I then can change serial code and enter new values for that and run the code so that its corresponding values go and sit in Table sheet again.

    I am attaching a sheet for clear understanding.

    Apologies for any grammatical/language errors. Hope I am making myself clear.

    Help me plz.
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Transfer Data to another sheet

    Hi Ravana

    This Code is in INPUT Sheet Module...click the Button...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Transfer Data to another sheet

    In the attached,
    =VLOOKUP($M$3,TABLE!$B$3:$C$23,2,FALSE) is used to return he Product Name

    =VLOOKUP($M$3,TABLE!$B$3:$AP$23,2*ROW(A2)-1,FALSE)
    reutrns Val1
    and, when dragged down, returns the various VAL's
    Attached Files Attached Files
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Transfer Data to another sheet

    Hi
    He
    re is a “VBA Array” code alternative.

    Note the basic code assumes
    _ 1) all serial Codes for a product appear in the Table in sheet “TABLE” ( This could be easily be changed
    _ 2) there are no duplicates in the Product name in the Table in sheet “TABLE”
    _ 3 ) the code is a lot bigger and more complicated than necessary. It can be simplified a lot, by taking out all the explaining ‘comments and substituting for many variables the values assigned to those variables where those variables are used and then removing the declarations to those variables. But i leave it in its full form for now as it may help you to understand the code.

    So , let’s say a bit of your INPUT sheet looks like this:

    Using Excel 2007
    Row\Col
    L
    M
    2
    DATA FEEDING TABLE
    3
    SERIAL CODE MS2
    4
    PRODUCT NAME MANGO2
    5
    VAR 1
    2
    6
    VAR 2
    4
    7
    VAR 4
    6
    8
    VAR 3
    7
    9
    VAR 5
    8
    INPUT


    _ .. then after running the code I give , a part of the Output TABLE sheet would look like this


    Row\Col
    C
    D
    E
    F
    G
    H
    I
    J
    K
    3
    PRODUCT NAME VAR 1 VAR 2 VAR 3 VAR 4
    4
    MANGO1
    5
    BANANA1
    6
    PEACH1
    7
    MANGO2 2 4 7 6
    TABLE
    _ .....

    Code:

    Please Login or Register  to view this content.
    _ ...................

    Please let us know how you get on and if you need further help
    Alan
    Last edited by Doc.AElstein; 12-05-2015 at 09:08 PM. Reason: .................. WONK ( poo )
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  5. #5
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Transfer Data to another sheet

    @ Jaslake and @Doc.AElstein

    Both the codes worked magic for me. I am indebted to you guys.

    @Jaslake & @Doc, can I ask you something? If in Input Sheet, my table doesn't start from L2 and say it starts from a50, where should I be making changes and how? And If I add another column in Table sheet between say Var2 and Var3, var5 and var6, will there be problem in code.?

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Transfer Data to another sheet

    Hi Ravana,
    _ 1 )
    Quote Originally Posted by Ravana View Post
    .....If in Input Sheet, my table doesn't start from L2 and say it starts from a50....
    _ That should be fairly clear if you look at this line in my code, and read the comments to the right:

    Let arrIn() = ws1.Range("l2").CurrentRegion.Value2’ .... CurrenRegion Property of range Object effecivelly returns a different Range Object which is a "box" catching all connected cells to the original range object ......

    What those 'comments are saying in effect is that l2 is any cell inside the table you are “capturing” into the Input Array , arrIn().
    I chose the Top Left Corner, just because that is often the convention with these things. Any cell within that Table would have resulted in capturing the same Range to the Array

    So for your new requirement A50 instead of L2 would be one possible choice

    Note for CurrentRegion Property to work,

    _ ....1a) you cannot have a complete empty row as the “gap” would cause for example this

    Let arrIn() = ws1.Range("a50").CurrentRegion.Value2

    to only capture the yellow “box” only in this table

    Using Excel 2007
    Row\Col
    A
    B
    C
    50
    DATA FEEDING TABLE
    51
    SERIAL CODE MS2
    52
    PRODUCT NAME
    53
    VAR 1
    2
    54
    VAR 2
    4
    55
    56
    VAR 3
    7
    57
    VAR 5
    8
    INPUT

    _ .... 1b) Similarly there must be at least one cell gap between things you do not want in the Input Array or, for example, this

    Let arrIn() = ws1.Range("a50").CurrentRegion.Value2

    would capture the yellow “box” in this table

    Row\Col
    A
    B
    C
    D
    49
    50
    DATA FEEDING TABLE
    51
    SERIAL CODE MS2
    52
    PRODUCT NAME
    53
    VAR 1
    2
    Somethhing
    54
    VAR 2
    4
    55
    56
    VAR 3
    7
    INPUT

    _ ...1c) there are, of course, many other ways to capture your input Array other than using CurrentRegion which would overcome those problems
    _ ......................




    _ 2)
    Quote Originally Posted by Ravana View Post
    ....And If I add another column in Table sheet between say Var2 and Var3, var5 and var6, will there be problem in code.?
    As long as the new VarX also appears in the Input table my code should work.

    For example if this is INPUT

    Using Excel 2007
    Row\Col
    L
    M
    2
    DATA FEEDING TABLE
    3
    SERIAL CODE MS2
    4
    PRODUCT NAME MANGO2
    5
    VAR 1
    2
    6
    VAR 2
    4
    7
    VAR 4
    6
    8
    VAR X
    X
    9
    VAR 3
    7
    10
    VAR 5
    8
    INPUT

    Then that will result in this being achieved with my code as it is unmodified

    Row\Col
    C
    D
    E
    F
    G
    H
    I
    2
    3
    PRODUCT NAME VAR X VAR 1 VAR 2
    4
    MANGO1
    5
    BANANA1
    6
    PEACH1
    7
    MANGO2 X 2 4
    8
    MANGO3
    TABLE

    _ 2b) The code could be easily modified for the case that not all VAR ‘s are in both sheets.

    Hope that helps, let us know if you need more help

    Alan

    P.s. The above demonstrates nicely why it is good to only give us a sample of your data. It is easier to show in the Thread, and in most cases any code we write for you would work for much more similar data with no, or little, modification. You just need to choose your data carefully to have typical formats and chosen so as to allow testing of all possible scenarios
    Last edited by Doc.AElstein; 12-06-2015 at 03:24 PM. Reason: ...Wonk..y typo#s and the such. ,)

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Transfer Data to another sheet

    Hi Ravana

    If you relocate the Input to Column A you'll need to change the Look Up Formula in B52 to...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In the Code two lines of Code require change...
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Complete revised Code...
    Please Login or Register  to view this content.
    This should have no impact...
    If I add another column in Table sheet between say Var2 and Var3, var5 and var6, will there be problem in code
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Transfer Data to another sheet

    Thanks a ton guys. You made my day. Can't express my joy in words.

    My wishes always with you guys.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Transfer Data to another sheet

    Quote Originally Posted by Ravana View Post
    Thanks a ton .....
    Your welcome, thanks for the kind words and Feedback
    Alan

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Transfer Data to another sheet

    You're welcome...glad I could help. Thanks for the Rep.

  11. #11
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Transfer Data to another sheet

    yep yep.. i know it is solved.. but.. just for fun... here's another that might work..

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Transfer Data to another sheet

    Hi ..

    Wow, with some multiple solutions this is a nice Thread.

    @ jaslake
    . Above and beyond. Amazingly quick Profi solution while i was still in the middle of mine, AND doing the extra modified Formula for the Op - ___ Respect.
    Alan

    _ .....................................

    @ apo..
    Quote Originally Posted by apo View Post
    yep yep.. i know it is solved.. ...........
    How very naughty of you.
    Quote Originally Posted by apo View Post
    ....... but.. just for fun.........
    You should be banned for high jacking a thread for your own amusement ( FWTFIW IMFO ( Wonk Poo ) )

    Quote Originally Posted by apo View Post
    ....here's another that might work.....
    _ .! Would be unlikely that your codes do not work ? And of course, it does..
    _ The famous apo / snb Type “one or two liner” compact code with an amazing amount of “hidden” processes inside it. Poo Wot
    _ I know you are not so keen on the following, but I just had to “open it up” to see what it is doing. So I destroyed it with my extra lines and annoying Green explaining ‘comments. I did two versions of the code.. I cant post them here – the Forum Editor just would not take the size. But it does demonstrate how much code knowledge is “hidden” in it!
    ( Also the 'comments in the VB editor, like in the Code window at ExcelForum go off to the right and are not too messy in the final code,- so you only see them if you want to and scroll to the right )

    _ I need those opened up Codes to understand what is going on, and maybe the OP could benefit, if he is interested in learning ( As Email Notifications are currently working, there is a chance he may “get the message”. )

    The two code versions are here:
    Code 1) ( Sub APooAlanWonkM5() )
    http://www.excelforum.com/developmen...ml#post4258399


    And here:
    Code 2) (Sub APooAlanWonkM4() )
    ( Note here i had to split the code into two posts to fit it in, But it is just one code: The second part should be copied into the same module as the first part and directly under the first part ! )
    Part 1
    http://www.excelforum.com/developmen...ml#post4258405
    Part 2
    http://www.excelforum.com/developmen...ml#post4258410


    I have split the codes in two parts corresponding to the main two lines:

    Please Login or Register  to view this content.
    I did two codes because when I first got to the second Part I thought the Rum had got to him again
    http://www.excelforum.com/excel-prog...ml#post4138803
    and he was mucking about with an over complicated lie to assign the output Array to the appropriate Worksheet range.
    Then i realised he had cleverly include the Food Product name in the initial “Data Array Captured” from the Input Sheet to reduce a further reference to the Input Worksheet later, hence abiding by a Golden rule of VBA Array type Codes of minimising the interaction with the Worksheet as most interactions are like slamming the brakes on in a code.
    So to help take the understanding in steps, the first code does it with needing that extra interaction later and hence simplifying the second Line considerably.





    Alan



    P.s. Maybe the Rum did get to him at the end, - he got the Column size a bit too big i think..### but I could be wrong, and anyway still a great code.... Maybe the code was inspired by the Rum – hence I refer to the clever section in the second code as the Rum section

    P.P.s.


    Some minor points I did not squeeze in
    _1) Output Array Declaration
    Dim x() As String
    in my code rather than
    Dim x_____( this is by default the same as Dim x As Variant )
    Generally This Array is produced or manipulated in codes of this form by Methods and Functions which return a Field of Variant values. So this would be done
    Dim x() As Variant
    The () tells VBA to expect a collection, ( Data Field or Array of values )
    Often carelessly or lazily this is done
    Dim x As Variant
    Pro’s like GC Excel have told me that.
    http://www.excelforum.com/excel-prog...ml#post3966493
    This unnecessarily uses a Variant Type which can also become an Array..
    However in the case of finally using the Split Function here we can use
    _a ) Dim x() As String
    Or
    _b) Dim x As Variant
    But not
    _c) Dim x() As Variant
    As
    _a) Is an Array of String Elements and _b) can become an Array of String Elements, but c) is an Array of Variant Elements which in apo’s code would lead to a type mismatch error. ( Or maybe apo was just was not too bothered niggly points after writing such a good code anyway ( and after the Rum had got to him..! ) )
    I am pretty sure that is correct as Kyle told me about the VBA Strings collection, to which the Split Function belongs
    http://www.excelforum.com/excel-new-...ml#post4083442

    _2) Lines 120 – 140 in
    Sub APooAlanWonkM5()
    I did not explain too well, to do with Range referencing , a very fundamentally but important Thing.
    Some references to elaborate there, in this order
    http://excelmatters.com/referring-to-ranges-in-vba/
    http://www.mrexcel.com/forum/excel-q...ml#post4038308
    http://www.excelforum.com/excel-prog...ml#post4183255
    http://www.mrexcel.com/forum/excel-q...ml#post4332606
    http://www.mrexcel.com/forum/excel-q...s-error-2.html
    http://www.excelforum.com/excel-new-...to-arrays.html

    _3) The “Magic code line” is better discussed in my Posts here
    https://usefulgyaan.wordpress.com/20...ication-index/
    And
    http://www.excelforum.com/excel-new-...ba-arrays.html

    And by snb here:
    http://www.snb-vba.eu/VBA_Arrays_en.html
    Attached Files Attached Files
    Last edited by Doc.AElstein; 12-08-2015 at 12:46 PM. Reason: ..... P_ W_ S...and

  13. #13
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Transfer Data to another sheet

    @Alan

    Thanks for the Rep.

    This is not kind...please don't denigrate Forum Contributors...they're offering help.

    IMFO ( Wonk Poo )
    @ apo..
    Quote Originally Posted by apo View Post
    yep yep.. i know it is solved.. ...........
    How very naughty of you.
    Quote Originally Posted by apo View Post
    ....... but.. just for fun.........
    You should be banned for high jacking a thread for your own amusement ( FWTFIW IMFO ( Wonk Poo ) )

  14. #14
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Transfer Data to another sheet

    Whats up doc.. (that one never grows old)...

    I have added some comments to my code and fluffed it out a heap.. creating several other arrays that will lead up to the creation of the array that is needed.

    Step though and follow it in the Locals Window... it should become clear.

    Please Login or Register  to view this content.
    @ jaslake
    This is not kind...please don't denigrate Forum Contributors...they're offering help.
    It's all good.. no offence taken..

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Transfer Data to another sheet

    Hi apo,
    Thanks for another contribution. It is probably is a lot better seeing your basic Explanation before delving into mine where I try to squeeze in every last detail!!

    This might be another in between step after your explanation before going into mine which is still a bit crammed with ( probably too ) much detail !

    Put data including heading in 1 D Array.
    Join the Elements with Carriage return so resulting in a "Horizontal" string.
    Replace the carriage return with two spaces.
    Split that using one space as a delimiter.
    So we are back to a Final 1 D Array but this time with an empty element between each original Element of initial Array.
    Find row for output.
    Output a slightly manipulated Final Array to miss out the Heading and first Space


    Please Login or Register  to view this content.
    _ ……………………………………………..

    P.s. I had actually never used the Locals Window. It does look like another good way to see what is going on. … and I can copy the contents so for example copy the string with the Carriage returns ( vbcrlf ) in and get paste it out thus

    "MANGO2
    2
    4
    6
    X
    _ . etc

    Which is again great to see what is going on.

    But some denigrating Points.

    _ 1 ) My local Window seems fixed in my VB Editor, and it is difficult to get that and the code easy to see at once.
    An Alternative is using the Watch Window ( Highlight Variables and Hit Shift + F9 then Insert ) and the Immediate Window ( Ctrl + G ) after writing
    Debug.Print after each variable
    It has the same effect, but the Windows seems not to be fixed in the VB Editor. It does however take more work to set up, so thanks for pointing me to the Locals Window

    _2) And then of course I would advise to look at my comments which say all that and a lot more in great detail! – but just to denigrate myself , it is a bit of a big step still !! – just needs to be stepped through in F8 whilst looking at the variables in the “ Watch” Windows as discussed….
    At the end of the day, as I tried to point out, there is a hell of a lot in that very nice compactly written code from apo – I hate him

    _3 ) Why not miss out x3 ? – Just join at x2 with a two spaces

    Put data including heading in 1 D Array.
    Join the Elements with two spaces between so resulting in a string of Elements with two spaces between.
    Split that using one space as a delimiter.
    So we are back to a Final 1 D Array but this time with an empty element between each original Element of initial Array.
    Find row for output.
    Output a slightly manipulated Final Array to miss out the Heading and first Space


    Please Login or Register  to view this content.
    P.S. For Jaslake and others. Just to set the record straight: All my comments ( denigrating and the such ) to apo are 100% in fun !!! . Like often i have learnt and benefitted greatly from his many additions to Threads, ( Only hope OP’s always get “The message” )

    But maybe it is enough now….. maybe?
    Attached Files Attached Files
    Last edited by Doc.AElstein; 12-09-2015 at 01:06 PM. Reason: Attatched Apo's (1) Testie

  16. #16
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Transfer Data to another sheet

    P.s. @ Ravana. Apo’s codes are great and I think are doing just as you want.
    I did just notice in parsing apo’s requires that the order of the VAR ‘s in Both INPUT Sheet and TABLE Sheet are the same. John’s and mine allow the order to both different in both Sheets, as demonstrated by my last two Screenshot in Post #6

    The corresponding screenshots for apo’s code in that scenario would be , based again on this in the INPUT Sheet

    Row\Col
    L
    M
    2
    DATA FEEDING TABLE
    3
    SERIAL CODE MS2
    4
    PRODUCT NAME MANGO2
    5
    VAR 1
    2
    6
    VAR 2
    4
    7
    VAR 4
    6
    8
    VAR X
    X
    9
    VAR 3
    7
    Sheet: INPUT


    _. Then after running apo’s codes, this for what you get your TABLE Sheet

    Row\Col
    C
    D
    E
    F
    G
    H
    I
    J
    3
    PRODUCT NAME VAR X VAR 1 VAR 2 VAR 3
    4
    MANGO1
    5
    BANANA1
    6
    PEACH1
    7
    MANGO2
    2
    4
    6
    X
    8
    MANGO3
    Sheet: TABLE

    _. As you see, the order of the VAR ‘s Values, 2 4 6 X … etc.. has been maintained form INPUT Sheet but this does not tie up with the order of the VAR Headings, which I have in a different order. Here again the result I obtain with both my and John’s codes

    Row\Col
    C
    D
    E
    F
    G
    H
    I
    2
    3
    PRODUCT NAME VAR X VAR 1 VAR 2
    4
    MANGO1
    5
    BANANA1
    6
    PEACH1
    7
    MANGO2 X
    2
    4
    8
    MANGO3
    Sheet: TABLE

    Alan


    P.S. ..... IMO you should never unsubscribe to a Thread you start! - You might miss something !

+ 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. [SOLVED] Issue regarding recorded Macro to transfer data from Input sheet to Storage sheet
    By Hirad001 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-29-2015, 04:26 PM
  2. Replies: 5
    Last Post: 12-30-2014, 12:41 PM
  3. Transfer data from input sheet to data sheet by using command button
    By vinh1985 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2014, 12:17 AM
  4. Replies: 5
    Last Post: 02-09-2014, 08:29 PM
  5. How to transfer data from a daily input sheet to a separate monthly total sheet
    By Jcooper71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2014, 02:37 PM
  6. Replies: 1
    Last Post: 03-11-2013, 05:37 AM
  7. Replies: 1
    Last Post: 02-13-2013, 01:32 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