+ Reply to Thread
Results 1 to 14 of 14

Copying parts of a large table into a new Sheet

  1. #1
    Registered User
    Join Date
    04-09-2007
    Posts
    25

    Copying parts of a large table into a new Sheet

    Hi,

    searching for a way to copy certain cell ranges from a main table into a different sheet (for nicer printing output, as in the main table there are also unused ranges) and in such a way that they would be copied there one after the other with no spaces between them.

    ( I have say A1:M1 with some cells for labels,
    then A2:M4 with a smaller table with some user choices etc. etc.

    then again A5:M5 with cells for labels
    and A6:M8 with another smaller table with user choices... )
    multiply by 2x

    Then I want to copy just those ranges that the User has selected something in - e.g. only A1:M4, if he selected something in A2
    or A5:M8, if he has selected something in A6

    Is this possible?

    Joe

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Try to use this code:

    Please Login or Register  to view this content.
    I hope it's what you want.

    Regards,
    Antonio

  3. #3
    Registered User
    Join Date
    04-09-2007
    Posts
    25

    Cool

    Indeed looks like it!

    Will test it and let you know!

    Thx inbetween, antoka05!

    Joe

  4. #4
    Registered User
    Join Date
    04-09-2007
    Posts
    25
    The example is working as it should.
    What I would yet need is a check performed on each A cell (down to row 250) and based on that certain range would be copied to the destination sheet.

    What I would yet like that Macro do:
    Start at A1

    **** Repeat (until Row 250)->
    Check if the cell is different than:
    1. ""
    2. "Leader Name Here"
    3. "Troop Name Here"
    If all three conditions are met, then:
    Check if the Cell is one of following: "HQ", "Elites", "Troops", "Fast Attack", "Heavy Support"
    If yes, then:
    Copy... (I think I can manage here)
    If no, then:
    If the cell on the same Row but in Column E has a numerical Vaule other than "0", then:
    Copy.... (again I think I can manage)
    If no then:
    Copy those 4 rows and columns A to M
    Move down 1 row
    <- Repeat *****
    Could you also help me with this?

    Joe

  5. #5
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Try this code:

    Please Login or Register  to view this content.
    Regards,
    Antonio

  6. #6
    Registered User
    Join Date
    04-09-2007
    Posts
    25
    Thx Antonio, will try!

    Joe

  7. #7
    Registered User
    Join Date
    04-09-2007
    Posts
    25
    Antonio, inbetween the testing and tailoring to my needs - is there a way to copy only current values of the cells? Because if I copy them with this macro, they get copied with functions etc., so give other results in the destination sheet.

    Something roughly like Rows(myRow).CopyValues ?

    Joe

  8. #8
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Try to replace the code:
    Please Login or Register  to view this content.
    with:
    Please Login or Register  to view this content.
    Regards,
    Antonio

  9. #9
    Registered User
    Join Date
    04-09-2007
    Posts
    25
    Hi Antonio,

    working like charm!

    Two more questions:

    - is it possible to alter the Font type and Size in the destination Sheet for every Range copied?
    - is it possible to combine multiple cells into one, so that I paste only one cell with values, separated with commas? - say cells from range "e:" & r.Row & "m" & r.Row ?

    Joe
    Last edited by joeyeti; 04-30-2007 at 05:34 AM.

  10. #10
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    To chnge font:
    Please Login or Register  to view this content.
    To combine cells:
    Please Login or Register  to view this content.
    I hope it's enough clear.

    Regards,
    Antonio

  11. #11
    Registered User
    Join Date
    04-09-2007
    Posts
    25

    Smile

    Yup, clear enough! Modified it a little, but just minor tweaks!

    Thx so far for helping me out!

    Joe

  12. #12
    Registered User
    Join Date
    04-09-2007
    Posts
    25
    Questions... there are never enough, are they?

    When copying the parts of the main table into the Print sheet, the screen keeps flickering to and fro between those two sheets. Is there maybe a way to do the copying on background?

    Joe

  13. #13
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Yes. Put this instruction at the top of the code:
    Please Login or Register  to view this content.
    and this instruction at the end of the code (one row before 'End Sub' row):
    Please Login or Register  to view this content.
    These instructions will avoid to see scrren updatings.

    Regards,
    Antonio

  14. #14
    Registered User
    Join Date
    04-09-2007
    Posts
    25
    Thx Antonio,
    working!

    Joe

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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