Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts

    VBA to copy the header and copy range

    Hi,

    Can you please help me with below problem here.

    1) "Main" sheet has header in row # 5.
    2) "Sub" sheet has header in row # 5.
    3) Header value in "Main" Sheet is something like "Apple", "Mango", "Peach" and so on.
    4) Header value in "Sub" sheet is something like "John", Steve", Roler and so on.

    What i am trying here is to VBA to search Main Sheet "apple and copy the entire column from Cell # 6 and below and paste it in Sub folder "John" column, row 6 below.
    Likewise do for all other header i require.. Like Mango and Peach.

    Please help

    Thanks,
    Shiva

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,197
    Thanks
    48
    Thanked 986 Times in 916 Posts
    That sounds like homework to me. Is it?
    We don't mind helping with homework, just like to know.

    cheers, Paul

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    do you want to copy to the sub sheet (located where????? sub folder...... Not at all clear here)
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Don

    I think there are two tab sheets, named [Main] and [Sub]

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi shiva

    There are many ways of doing this, depending on your particular situation.
    In the attached example, data from columns on sheet [Main] is copied to specified columns on sheet [Sub].
    In my example, I used row1 on sheet [Main] to specify the particular destination column heading of sheet [Sub]
    I used a formula in row2 to determine the corresponding column number to copy-to.
    Then this vba routine to do the copying:
    Code:
    Sub copyData()
    
    Sheets("Main").Select
    
    'Find last data row, using column [A]..
    zLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For Each cell In [A5:K5]
    zCol = cell.Column
    
    zCopyToCol = Cells(2, zCol)
    If zCopyToCol <> "" Then
    Cells(6, zCol).Resize(zLastRow, 1).Copy Sheets("Sub").Cells(6, zCopyToCol)
    End If
    Next cell
    
    [a1].Select
    
    End Sub
    see attached example file.

    zeddy
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi shiva

    ..you could easily modify the vba code to set the required column range e.g. from [A5:K5] to whatever you need.

    zeddy

  7. #7
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Wow, thats is something new I learned Zeddy,. "Putting Destination # as formula and then Use VBA to do its work. "

    My challenge is, " "Sub" sheet John column can change its place in random file. So i would need to Find header and paste data.
    Note: Its only 6 coloumn from "Main" sheet data will be copied, and find 6 Column of "Sub" sheet. Now Sub sheet has 25 columns and it may change its palce.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi shiva

    In my example file, move column 'John' to any column in row6 of sheet [Sub]
    The formula in row2 will then 'find' where the column 'John' is.
    see attached example 2 file.

    Note: Its only 6 coloumn from "Main" sheet data will be copied, and find 6 Column of "Sub" sheet.
    I'm not sure I understand this.
    Can you explain a little more please.

    zeddy
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I was just trying to be more clear by saying, it is just 6 items that it will be looking and copy pasting in main sheet.

    Your code works like charm, but my problem here is, i cannot make changes in the Main sheet. Code has to be either in VBA or in Master sheet where I am going to put colorfull button to run various macros. Like in your attachement, I will be using Sheet 3 as my master sheet with macro button in it.

    Regards,

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi shiva

    Thanks for clearing that up.
    OK. So there are only 6 separate columns on sheet [Main] to be copied.
    Are these 6 columns always in row 5?
    Are these 6 column always together on sheet [Main] (for example, in [A5:F5])?

    I have another method that will do this.

    zeddy

  11. #11
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Main file would always have 8 column from which I need to copy the data.. A5:O5. However, F,G,H column is hidden as it was not required.

    Hope this helps.

    Thanks a lot for your assistance.

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi shiva

    [A5:O5] is 15 columns; less 3 hidden columns (F,G,H) = 12 columns
    Where are the 8 column from which I need to copy the data ?
    I need to know, to give you the easiest solution.

    And, just to confirm, you 'know' what the corresponding destination column 'headings' are on sheet [Sub] for each of the 8 source headings on sheet [Main] (but these destination columns could be in any order, on row 5 of sheet [Sub] ).

    zeddy

  13. #13
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts
    my bad. J,K,L column is also hidden.

    It can be anywhere in Sub sheet A5:X5.

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi shiva

    OK. Got it.
    It's a little late now, so I'll test and post back my file tomorrow.

    zeddy

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi shiva

    see attached file.

    This method displays a Form to allow you to select the destination column heading on sheet [Sub] for each of the source heading column on sheet [Main].

    This uses named ranges assigned to each of the headings on both sheets.
    In this version, you can only copy to-and-from visible columns.

    Technically, we don't need a dropdown for the source column selection.
    We could just use a pre-filled textbox for these values.
    But I left them as dropdowns, for general use with this method.

    Please let me know if this is does what you want.

    zeddy
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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