+ Reply to Thread
Results 1 to 96 of 96

Syntax for referencing multiple ranges when using copy & paste feature

  1. #1
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Syntax for referencing multiple ranges when using copy & paste feature

    Good morning. I'm attempting to use the following code to copy multiple ranges from one Excel workbook and paste the data into a newly created Excel workbook. It works perfectly except I'm struggling to find the proper syntax for referencing multiple ranges in the originating workbook. Thanks for chiming in with any feedback you might have.

    Please Login or Register  to view this content.
    The second range I would like to copy is ("Demographics").Range("E18:H32") and I'd like it to paste into the new workbook before the data already referenced in the above code.

    Thanks.

    Matthew

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Matthew--
    Where specifically, do you want to paste the second range.

    I'd like it to paste into the new workbook before the data already referenced in the above code.
    I am not sure what this means exactly. Also, do you need to paste special or just paste?

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    alansidman,

    For the sake of our example, let's say that the data in range one is a poem about pigs and the data in range two is a list of questions about the poem. When the two ranges are copied from the originating workbook, I'd like them to paste with the poem at the top of the new sheet in the new workbook and the questions under the poem.

    Hope that makes sense. I'm thinking that the order the ranges are placed in the code is the order they will paste into the new workbook but I don't know that for sure.

    Thanks.

    Matthew

  4. #4
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Are you certain about this Range??
    The second range I would like to copy is ("Demographics").Range("E18:H32")
    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.

  5. #5
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Assuming the Ranges you've defined, try this
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Try this:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    Are you certain about this Range??
    John,

    It's been some kind of Monday. It's always fun coming back to work after a few days away from work. Busy, busy, busy.

    You were spot on for questioning the range I had selected in my earlier post. I applied the code you supplied below (with modified ranges) and love the result. I'm going to try my hand at doing two modifications and I'll let you know how things go: remove the shading in the cells after paste and adjust column width to fit content after paste.

    BTW, it's pretty cool that you caught by range selection error....Thanks.

    Matthew
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by alansidman View Post
    Set NewBook = Workbooks.Add
    lr = NewBook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    wbk.Sheets("Demographics").Range("E18:H32").Copy
    NewBook.Sheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
    wbk.Sheets("Presenting Problems & Progress").Range("A1:H100").Copy
    NewBook.Sheets("Sheet1").Range("A1" & lr + 1).PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    End Sub[/CODE]
    Alan,

    Thanks for spelling out the syntax for me. So if I'm understanding things correctly, in theory, I could reference as many ranges as I wanted to as long as I do .Copy, .PasteSpecial, .Copy, etc.? I was attempting to copy all my ranges before pasting which is what I believe was causing things not to work.

    BTW, I was over by Woodbury Pond and Little Purgatory Pond the other day, when we still had a TON of ice, and you'll be happy to hear, the ice is out and the water is warming up.....kinda sorta.

    Thank.

    Matthew

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Matthew;
    Thanks for the update on the Ice. But I am sure the roads are still posted.

    Any way, another way around the copy and paste special events may be to use the Union function to grab a bunch of ranges and then paste them. Do a little bit of google research on this when you have time, or start here

    http://www.cpearson.com/excel/BetterUnion.aspx

  10. #10
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Alan,

    Yup, roads are still posted and thanks for the lead on the Union Function. I will do some homework. Thanks again.

    Matthew

  11. #11
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I just received an "Ambiguous Name Detected: UpdateCopyofITP_Click" popup message when I was doing a dry run of the form. This pop up occurred when I clicked the UPDATE "Copy of ITP Goals & Obj." command button at the bottom of Sheet ITP Goals & Obj. I back tracked and the popup began appearing between V2.5 and V2.6. I'm heading home and will attempt to "debug" this one and find the source that's causing the ambiguous name. I'm guessing it's a simple fix. I'll let you know once I've got it fixed or hit a wall. Thanks.

    Matthew

  12. #12
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Changing the Demographics Range to be copied from E18:H32 to A18:H32 causes issues. I had originally written the Code to accommodate this. The Code will involve merging some cells. Try your hand at it...get back to me.

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew,

    With a message of:
    "Ambiguous Name Detected: UpdateCopyofITP_Click"
    It sounds like you have two subroutines with the same name. I was reading the other day that we need to also be aware that our subroutines and sheet names and named ranges should also be different names. There could be some Ambiguous Names if we don't watch out.
    Last edited by MarvinP; 04-28-2014 at 05:57 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  14. #14
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Take a look at Module 11...you have a an empty Macro...get rid of it
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John & MarvinP,

    You were both on the money.....with a twist. Please don't as how I did this (insert red face here) but somehow I replaced all the macros in Module 7 with a duplicate copy of the UpdateCopyOfITP_Click() macro from Module 6. I copied and pasted the macros from an earlier version back into Module 7 and I'm back to full speed ahead. Thanks.

    Now, off to work on the copy/paste into a new workbook portion of the code. Thanks again.

    Matthew

  16. #16
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by MarvinP View Post
    I was reading the other day that we need to also be aware that our subroutines and sheet names and named ranges should also be different names. There could be some Ambiguous Names if we don't watch out.
    MarvinP,

    As the code for my form has become more and more complex and intertwined I can see how easy it can become to names mixed up and duplicated. Did the article suggest a method for keeping things straight?

    Thanks.

    Matthew

  17. #17
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Hey Matthew,

    I like PUP tools from John Walkenback
    http://spreadsheetpage.com/index.php/pupv7/home
    But I think there is an alpha sorter for Procedures built into the MZTools
    http://mztools.com/v3/download.aspx

  18. #18
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Thanks, MarvinP, I'll take a look at the links.

    Matthew

  19. #19
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    So far this is what I've done:

    1) added a new worksheet titled "CSR Template"

    2) added "CommandButton 5" and assigned it the macro ("ExportCSR_Click()") that use to be assigned to the "CommandButton 1" in the userform; this is the code that copies and pastes specifies values from our workbook into a new workbook

    3) adjusted the macro ("ExportCSR_Click()") to suit; (a) in the New Workbook I renamed "Sheet1" to "DISCHARGE PLANS" and "Sheet2" to "PROGRESS ON GOALS" and (b) I shifted the values pasted into the New Workbook on Sheet "PROGRESS ON GOALS" up to the top of the sheet

    4) added a new macro ("CreateCSR_Click()") and assigned it to CommandButton "Create CSR" in the userform; this macro unhides worksheet "CSR Template"

    Module 10 code assigned to CommandButton 5 on Sheeet "CSR Template" (hidden)

    Please Login or Register  to view this content.
    And in the userform:

    Please Login or Register  to view this content.
    I've hit a wall on the next step of code modification. Let me explain what I'm trying to do next:

    1) I'd like the values that are pasted into the New Workbook to all be aligned along the left side of the sheets (text in rows to start in Column A).

    2) The code I added to Sub ExportCSR_Click() in Module 10 is not "hiding" worksheet CSR Template as I had hoped; not sure how to fix this.

    3) I would like to add code to Sub ExportCSR_Click() so that when CommandButton "Export CSR Info" is clicked the contents of worksheet "CSR Template" are copied and pasted to Sheet 3 (renamed "CSR Info") in the new workbook that contains the other values we're copying and pasting into the new workbook.

    With any luck I haven't totally confused you. If I have, let me know and I'll get back to you. I'm doing the second "roll out" training of the form today at 9 so I'll be out of pocket for about an hour or two. Talk soon.

    Matthew

    Thanks.

    Matthew
    Attached Files Attached Files
    Last edited by moosetales; 04-29-2014 at 08:52 AM.

  20. #20
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I think perhaps you attached the wrong Workbook...I see nothing of what you describe in your attachment.

  21. #21
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I just re-posted the correct version. I'm disheartened...I had the code working and I must have edited something accidentally in the code before saving it cause I'm now getting a error when I try to Export the CSR Info using the newly added CommandButton on the CSR Template worksheet. Off to training. Thanks.

    Matthew

  22. #22
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I think the attached addresses the three issues. Not sure where you're going with this...
    Attached Files Attached Files

  23. #23
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    I think the attached addresses the three issues. Not sure where you're going with this...
    John,

    Yes, my initial run through of the modified code does as expected. Let me see if I can explain the logic behind this latest twist:

    The State contracts with a managed care company (APSHealthcare) who we work with to obtain insurance authorization for treatment when a client comes into services. Initially we are given anywhere between 3-6 months worth of authorization to provide treatment. At the end of that authorization period we must go back to APSHealthcare to request additional authorization to continue treatment.

    To request additional authorization my crew collects clinical data from the previous authorization period and submits that information to APSHealthcare through a web based portal; we refer to this process as submitting a Continued Stay Review (CSR). Much of the information we currently submit to APSHealthcare comes directly from the treatment plan; i.e. discharge plan, progress on goals and objectives, number of weekly hours worked with the client, etc.

    At the time of a CSR, my users are also required to answer a list of additional questions that APSHealthcare requires ahead of approving additional treatment authorization. This is where the CSR Template Sheet enters the equation.

    When all is said and done, my users will be able to copy the information contained in the newly created workbook and paste it directly into the APSHealthcare portal without having to make any further modifications.

    I hope this makes more sense than not. I'll spend some time with the code in a bit and will be in touch. Thanks again for sticking with this project. My users are jumping with joy over the newly added CSR Template functionality.

    Matthew

  24. #24
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Makes sense...glad your Users are pleased.

  25. #25
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    MarvinP suggested this...use it most every day. If you're doing Development Work it's a must have.
    .http://mztools.com/v3/download.aspx

    I'd also suggest these...
    http://www.asap-utilities.com/...use this one on your File(s) every time I open it
    http://www.oaltd.co.uk/Indenter/IndentPage.asp...another must have

  26. #26
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Thanks, John for the leads. I am thoroughly enjoying all aspects of this project. Thanks again.

    Matthew

  27. #27
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Good morning. It was a long night, but fruitful. After testing the structure and layout of the newly added CSR Template I realized last night that the way I was going about it would not work seamlessly with the web based portal my users will need to be copying and pasting into. So, I set about to restructure things a bit.

    I'm a bit apprehensive about this post as I feel as though I may confuse the heck out of you.

    Basically what I've done is added all the questions that need to be answered for the CSR to the CSR Template worksheet. Then I've used formulas to have specific values from cells throughout the ITP form "auto populate" into specified cells in the CSR Template Worksheet. I modified the export commandbutton on the CSR Template to copy only the content on the CSR Template.

    So, I've hit a wall using formulas to reference cells that contain information that do not have a "set" cell reference; in other words, anything that is added to the form by way of an ADD button.

    I've attached a CSR Template Demo workbook that's a carbon copy (not only words but cell placement) of the sheet by the same name in the ITP form. The demo worksheet just shows where in the rest of the ITP form I'm trying to pull information from for each individual cell in the CSR Template.

    QUESTIONS
    1) Is there a way to still use formulas to "auto populate" specific cells in the CSR Template worksheet or will I need to use VBA?
    2) I noticed that I can now copy and paste a cell without double clicking into the cell first. Is that something I've done or is another portion of the code overriding that portion of our code

    Thanks, as always.

    Matthew
    Attached Files Attached Files

  28. #28
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Regarding this
    I noticed that I can now copy and paste a cell without double clicking into the cell first
    I turned that feature off for testing...forgot to turn it back on. These three lines of Code in Workbook Open
    Please Login or Register  to view this content.
    Regarding this
    Is there a way to still use formulas to "auto populate" specific cells in the CSR Template worksheet or will I need to use VBA
    You posted a Demo Worksheet...show me what that Demo will look like based on the Data in HCT-RCS ITP V2.21.xlsm

  29. #29
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Once out of a string of meetings I'll post an example. Thanks.

    Matthew

  30. #30
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I think I've got the sample document all set. I used the ABCs as values throughout the treatment plan and CSR Template sheet to demonstrate where things are supposed to go. Let me know if it's clear or still murky what I'm trying to accomplish. Thanks.

    BTW, when you open the form, click on the "Show CSR Info" button and you should be redirected to sheet CSR Template.

    Matthew
    Attached Files Attached Files

  31. #31
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    On Sheet "Presenting Problems & Progress" rows 5 to 50 hide when OB "Initial ITP" on Sheet "Demographics" is clicked and I can't figure out how to adjust the range that gets hidden. The problem is, when I add more than 4 Presenting Problems anything past row 5 gets hidden. Here's the code....and yes, I've tried adjusting the row I've highlighted in green but I still can't get it to work. Thanks.

    Please Login or Register  to view this content.
    In the attached version of our form I've added code to do the following:

    1) remove the extra blank sheets from the NewBook workbook that is created when CommandButton "Export CSR Info" is clicked
    2) added code to control the row height in the newly created Sheet "CSR Info"

    Please Login or Register  to view this content.
    Talk soon.

    Matthew
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Test....Test....Test

    (I had not been able to see page three of this thread so I tried a test post and up popped page three. Anyone else ever had this issue in the forum?)
    Last edited by moosetales; 05-01-2014 at 08:49 AM.

  33. #33
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Change your Button Code like this
    Please Login or Register  to view this content.

  34. #34
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Thanks for pointing out the code for OptionButton 125. I totally overlooked the code for that button. No wonder nothing was happening when I adjusted the rows in the SyncGoals code. BTW, you've come up with a much better solution for the hide/show function. Now my users will be able to enter any number of Presenting Problems and not have to worry about some being hidden.

    Thanks again.

    Matthew

  35. #35
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    There must be no more changes to CSR Template. If you've made changes since Version 2.23 I need them ASAP.

  36. #36
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Ok, understood. I don't see any changes between 2.23 and 2.24 but in case I missed something here is the most recent version. I've already explained to my users that this is the last adjustment to the form until we've had a chance to use it extensively for the next few months. I can see how a Project Manager is key to the process of program development. Thanks.

    Matthew
    Attached Files Attached Files

  37. #37
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I just had a TERRIBLE thought...what's the CSR Template look like if there's two or more STG's?

  38. #38
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    what's the CSR Template look like if there's two or more STG's?
    John,

    Here you go. Take a look at the attachment. Thanks.

    Matthew
    Attached Files Attached Files

  39. #39
    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: Syntax for referencing multiple ranges when using copy & paste feature

    That's what I was afraid of...this'll take a while...if I can do it...

  40. #40
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    That's what I was afraid of...this'll take a while...if I can do it...
    John,

    Before you go down that path......what would be a more straight forward approach to transferring that info to the CSR Template? I'm open to suggestions. If this change is going to cause significant complications let's re-think things....I'm good with going to a simpler approach. Thanks.

    Matthew

  41. #41
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    What if we modify the code that's associated with CommandButton 11 (SyncGoals) and Option Button 126 and Command Button 18 (UpdateCopyITP)? If this would be more straight forward I can make this work. I'll see what I can come up with and will be in touch. Thanks.

    Matthew

  42. #42
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    The attached version of the form is JUST an example. I've added a CommandButton on the Demographics Sheet (upper right corner). This is a quick and dirty job just for "proof of concept".

    Basically, for Sheet "ITP Goals & Objs. what I'm proposing is to copy/paste columns C & D, starting on the row that contains the first STG and ending on the row that contains that last commentary about "How parents will be involved..." I think we could do the same thing for Sheet "Presenting Problems & Progress". I can modify the flow of the CSR Template to accommodate this approach.

    If this approach is viable let me know and I will set to work at trying my hand at a more careful modification of the code. Thanks.

    Matthew
    Attached Files Attached Files
    Last edited by moosetales; 05-01-2014 at 04:36 PM.

  43. #43
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    This is your project. You need to make it work for you and your Users. A simpler approach is always better if it gives you the information you need.

    The original approach will take a LOT of Code and a LOT of manipulation to get it done. Furthermore, if you would need to revise the template in the future it would be a mess to do so.

    I think a redesign is in order...I'll look for it...

  44. #44
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I'm heading home so I'll be out of pocket for the next hour. I've reconfigured the CSR Template; Sheet "CSR Template" for updates highlighted in red and yellow. I'll start work on this once kiddos are down and I can focus my efforts.

    This redesign will work great...not sure why I over complicated it the first time around Thanks.

    Matthew
    Attached Files Attached Files

  45. #45
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I think that's looking rather straightforward. Let me know when you've finalized it and we'll automate it.

  46. #46
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Okay, I think I've got things settled. Here's what I've got going:

    I have three separate ranges I need to copy from Sheets "Presenting Problems & Progress" and "ITP Goals & Obj." and paste into Sheet "CSR Template"


    #1 COPY from Sheet "Presenting Problems & Progress" A2:B* PASTE to Sheet "CSR Template" Range A3:B3

    #2 COPY from Sheet "Presenting Problems & Progress" A11:B* PASTE to Sheet "CSR Template" Range A7:B7

    #3 COPY from Sheet "ITP Goals & Obj." C9:D* PASTE to Sheet "CSR Template" Range A15:B15

    (* = the last row in a range)

    Values such as No 1, STG 1, Obj B, STG 2, Obj C, Progress will be pasted into column A in Sheet "CSR Template".

    My thought is to add code to the "Show CSR Template" button in the Userform that would autopopulate select cells in Sheet "CSR Template" and show Sheet "CSR Template"

    Then, we could add code to the "Export CSR" button on Sheet "CSR Template" that would copy Columns A-C into a new workbook.

    I hope this makes sense. I tried my hand at the code but what you call "straight forward" I still get turned around in fairly quickly. Thanks.

    Matthew
    Attached Files Attached Files
    Last edited by moosetales; 05-02-2014 at 06:27 AM.

  47. #47
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Try this Code in the attached. The Code is in Module Test2. The Original Template is kept in tact; a Copy is created and all the dirty work is done on the Copy. The Copy is then deleted.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  48. #48
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I've been sitting in a management meeting all morning, knowing a revised version of the form was up and ready to test......torture to say the least.

    I've been through the form and it looks FANTASTIC...not to mention how it flows! You have outdone yourself here. At the close of the management meeting I gave some of my peers/users the revisions and they were blown away. I need to run the form through it's paces this weekend and I fully expect to have ALL users up and running on this form come Monday.

    With this latest revision, I'm going to mark this thread SOLVED.....but it's with great reservation I do so. I have enjoyed working on this form for the past two months immensely. I have learned more than I could ever have imagined possible in this short amount of time....in LARGE part thanks to you.

    Thank you for being patient, willing to teach, responsive, encouraging and generous with your time, talents and knowledge. I've posted two versions of the form with this form.....current version with a couple tweaks and the very first version I posted a little over two months ago. Talk about a whale of a difference.

    I'll let you know how things go after I've been able to put the form through it's paces this weekend. Take care and talk soon.

    Matthew
    Attached Files Attached Files

  49. #49
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Let me know...

    For your edification...notice
    #1 COPY from Sheet "Presenting Problems & Progress" A2:B* PASTE to Sheet "CSR Template" Range A3:B3

    #2 COPY from Sheet "Presenting Problems & Progress" A11:B* PASTE to Sheet "CSR Template" Range A7:B7

    #3 COPY from Sheet "ITP Goals & Obj." C9:D* PASTE to Sheet "CSR Template" Range A15:B15
    These are done in reverse order...tell me why...
    Last edited by jaslake; 05-02-2014 at 06:41 PM.

  50. #50
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    These are done in reverse order...tell me why...
    Good morning, John. Let's see if I can answer your question....

    The order of information going into Sheet CSR Template is tied directly to the purpose for which we are collecting the information on Sheet CSR Template. The information we are pulling together on the CSR Template is used when requesting authorization to continue treatment with a client.

    We were given a mock template, from APSHealthcare, that showed the information and the order in which their reviewers would like to see information entered into their portal. Since the CSR Template is being submitted at the end of one authorization period and before we start the next authorization period, APSHealthcare reviewers want to see:

    1) what are the current presenting problems
    2) how the client and family have been involved/responding to treatment
    3) what goals we've been working on
    4) the progress on those goals
    5) what goals we plan on working on during the next authorization period
    6) what methods will the worker use to address these goals and
    7) how will the parents be involved

    Let me know if this does/doesn't make sense and I'll take another crack at it.

    I've made a few (minor) tweaks and so far so good. Thanks again.

    Matthew
    Attached Files Attached Files

  51. #51
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I wasn't asking you to justify your "need" for the Template.

    This was a "teaching/learning" type question.

    Take a look at the Code and you'll see the Template (copy) is being filled from the Bottom Up. See if you can figure out why.

    Clue: What happens if it were to be filled from the Top Down?

  52. #52
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    This was a "teaching/learning" type question.
    Oops....misread that one Let me take a look at the code and see if I can figure it out. Talk soon.

    Matthew

  53. #53
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    It's finally sunny for a change so I'm heading out for a bit and when I'm back I'll take another look at the code. For starters, does it have something to do with a "place holder"? Thanks.

    Matthew

  54. #54
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Look at the Code...you tell me.

  55. #55
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Okay, I think I've got it.....

    Please Login or Register  to view this content.
    The above lines of code that are highlighted red are for lack of a better term, "place holders" or reference points. If the code were written in reverse those reference point would no longer be accurate since the data that was pasted above would have shifted everything down.

    For example, by doing the code the way you wrote it, by the time data is pasted into A7 the A15 reference point is no longer needed since data has already been pasted into that spot.

    Am I close? If not, give me another clue and I'll keep digging. Thanks.

    Matthew
    Last edited by moosetales; 05-03-2014 at 08:33 PM.

  56. #56
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I'm color blind but this is right on point
    If the code were written in reverse those reference point would no longer be accurate since the data that was pasted above would have shifted everything down.
    Good catch...

  57. #57
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I would never have caught that had you not prompted me to take a look. Thanks for the tutorial. BTW, I've updated my previous post so you can tell which lines of code I shaded red.

    I have a user trialing the newest version this weekend and she just sent me an email with some feedback. I'll let you know what the over/under is on the revisions. Talk soon.

    Matthew

  58. #58
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Good morning. After a few test runs last night I realized I needed to adjust my CSR Template. So, with your previous guidance I was able to recode everything I needed with one exception. For the purpose of clarifying which rows I need to copy I created a new range on Sheet "Presenting Problems & Progress" titled "CSR_Review_Questions". I need that range copied and pasted into my CSR Template on row B5. I tried my hand at it but I'm striking out left and right. I've indicated below which of the lines I added that aren't working as expected. What am I missing?

    Thanks.

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

  59. #59
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Replace your section of new Code with this...notice the changes...figure out why
    Please Login or Register  to view this content.

  60. #60
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    It's been a Monday so I haven't been able to dissect the code yet but I will and I'll let you know what I find. I'll be in touch.

    Matthew

  61. #61
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Good evening. Here's what I've come up with so far:

    Please Login or Register  to view this content.
    Am I close?

    Matthew

  62. #62
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    You're very close...see this regarding the Find Method
    http://www.rondebruin.nl/win/s9/win006.htm

  63. #63
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Okay, round II.....


    Please Login or Register  to view this content.
    Works for when there is only one occurrence of the value in the specified range...it will select the first cell in the range with the value.

    Please Login or Register  to view this content.
    Works for when there are multiple occurrences of the value in the specified range...it selects the last occurrence.

    Since my range only has one occurrence of the specified value in the range your code is in order.


    And the answer is?

    Matthew

  64. #64
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Which occurrence do we wish to find (assuming there MAY BE multiple)??? ...The Code happens to find the First...which is what we want...

    Please note...there are samples in the Code where we used this...why???
    Please Login or Register  to view this content.
    Last edited by jaslake; 05-05-2014 at 09:42 PM.

  65. #65
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    So basically, if the code found and copied/pasted the second one in the range it would paste incorrectly?

    Here's the code from Module Test 2 I think you were referencing above:

    Please Login or Register  to view this content.
    Does the (1) after .Cells refer to the first value found in the range? Since there are multiple No in this range I believe we want all values to be found and pasted starting with the first one found in the range?
    Last edited by moosetales; 05-05-2014 at 10:28 PM.

  66. #66
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I've found a cell border that is pasting into the NewBook after the Export CSR INFO commandbutton is clicked; Sheet "CSR Info" C21 & C22 (right side border of the cell). I have yet to find the source in the code that's allowing this through. Any thoughts on why this might be happening in this spot? Thanks.

    Matthew
    Attached Files Attached Files

  67. #67
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I'm sure there's a reason for it but I'm not going to look for it...this modification clears the Borders.
    Please Login or Register  to view this content.

  68. #68
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I was so close to reworking the code to clear cell borders but I now see I was attempting to place the code in the wrong place. Thanks.

    BTW, did I answer the why correctly in the previous post?

    I just got word from users that the can't print in a late note feature is not working. I've looked and can't seem to locate the following lines of code that prompted this feature in earlier versions. I added the following lines of code (from a previous version) to the BeforePrint macro. It seems to be working but I wanted to ask if this was the correct placement of the code? Thanks.

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

  69. #69
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    If it ain't Broke don't fix it
    It seems to be working
    Regarding this
    did I answer the why correctly in the previous post
    The Find Method is not intuitive...this Code searches from the Bottom Up...cells(1) is examind last
    Please Login or Register  to view this content.
    This Code searches from the Top Down
    Please Login or Register  to view this content.
    With this Code we know where the First "No" is, it's in A2 and does not move. We want to find the Last Incident of "No" so we know what range of cells to Copy
    Please Login or Register  to view this content.

  70. #70
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    With this Code we know where the First "No" is, it's in A2 and does not move. We want to find the Last Incident of "No" so we know what range of cells to Copy
    Please Login or Register  to view this content.
    So if the last No on the worksheet was in B22, A2:C22 would be copied and pasted wherever we wanted it to go. And, am I correct in saying, when using After:=.Cells(1) I'm going to indicate the upper most known cell in the range whereas when using the After:=.Cells(.Cells.Count) I'm going to use the lower most known cell in the range?

  71. #71
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Just got word from another user that a Run Time Error 91, Object Variable or With Block Variable not set when ObjectButton125 is clicked twice in a row.

    Here's the code as it is currently:

    Please Login or Register  to view this content.
    I added a line of code and it appears to be working:

    Please Login or Register  to view this content.
    Again, just checking to see if for one reason or another this additional code might negatively affect something down the line that I'm not thinking of. Thanks.

    Matthew

  72. #72
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Probably ought to add an additional line...test it
    Please Login or Register  to view this content.
    Regarding this...I told you it's not intuitive
    So if the last No on the worksheet was in B22, A2:C22 would be copied and pasted wherever we wanted it to go. And, am I correct in saying, when using After:=.Cells(1) I'm going to indicate the upper most known cell in the range whereas when using the After:=.Cells(.Cells.Count) I'm going to use the lower most known cell in the range?
    When using After:=.Cells(1) the Code will go to the LAST cell in the Range and begin looking UP until it finds what we're looking for.

    When using the After:=.Cells(.Cells.Count) the Code will start in the FIRST cell in the Range and begin looking DOWN until it finds what we're looking for.

    Confusing I know...as I say...not intuitive.

  73. #73
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post

    Probably ought to add an additional line...test it
    Appears to be working well and stable.

    Quote Originally Posted by jaslake View Post
    Confusing I know...as I say...not intuitive.
    Yes indeed it is. Thanks for spelling it out for me.

    Matthew

  74. #74
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    After a few last minutes tweaks, here is the "final" version I sent out to all users company wide. If I've learned anything, it's that I'm sure something will come up that will need my (our attention) but I'm fairly confident in the stability and user-friendliness of our form. Thanks again for ALL your hard work on this project and for giving me many pointers along the way. Talk soon.

    Matthew
    Attached Files Attached Files

  75. #75
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew
    You're welcome.

  76. #76
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Hi John,

    I ran into an unexplained circumstance with the form today. In a training, for one of my users who's running xl2007, I noticed that on Sheets Presenting Problems & Progress the "ITP REVIEW" command button that I had hidden under a white filled text box appears visible whereas on my machine (and as far as I can tell on any machine running 2010) the command button is hidden. I unprotected the form while on her machine and attempted to send the command button behind the text box (or vise versa) and I could not hide the button.

    Would you have any idea why this is happening? I guess it's not a big deal but I'd like the form to be as consistent between platforms as possible. If I'm not mistaken, we need that command button to be where it is because it acts as a "place holder", right? Thanks.

    Matthew

  77. #77
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    On my platform the same behavior is observed. Why? I have no idea.

    Regarding this
    If I'm not mistaken, we need that command button to be where it is because it acts as a "place holder"
    I have no idea what that Button does or why it's there. I believe it's your creation. Move it where you like...see what happens.

  78. #78
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Good evening. You know the story about the mother who asks her daughter why she cut off the end of the ham before she put it into the oven? We'll as the story goes the daughter states something to the effect of, "We'll, mom, that's what you used to do when we were growing up.", to which the mom states, "Yes, because I did not have a large enough pan."

    We'll, I've kept that darn ITP Review button in the same spot since it was introduced into the form back in V1.8 solely based on my belief that it needed to be in that specific row/column as a "place holder" of sort. We'll I just went back to the first version it appeared in and moved it all over the page and it worked exactly as it was supposed to, no matter where it was placed.

    The only thing I can think as to why I thought the button's position was critical is that on the ITP Goals & Obj. page we did use Button 10 as a place holder of such at one point in our code.

    Oh well, I've removed it from the form and the problem is solved.

    Thanks for getting back to me earlier.

    Matthew
    Attached Files Attached Files
    Last edited by moosetales; 05-07-2014 at 10:10 PM.

  79. #79
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    In general, any Button that Inserts or Deletes will more than likely be used in the Code as you call it, a "place holder". Other Buttons are "probably" irrelevant as to where they're located...IN GENERAL...not absolute...although I'd bet on it.

  80. #80
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    In general, any Button that Inserts or Deletes will more than likely be used in the Code as you call it, a "place holder". Other Buttons are "probably" irrelevant as to where they're located...IN GENERAL...not absolute...although I'd bet on it.
    Good to know. BTW, what is the actual word used for what I've been referring to as a "place holder"? Thanks.

    Matthew

  81. #81
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    "Place holder" is as good a description as any.

    In my mind the Code is asking for the upper left Cell Address of the Button that was clicked. So, again, in my mind, the Code is asking "What Button was clicked".

    It tells us where the Button is located on the Worksheet...call it "place holder" if you wish...it's as good as any description as long as you understand what it's doing and why.

    Edit: Once you know the upper left Cell Address of the Clicked Button you can use that address to perform other actions...that's indeed a "place holder".
    Last edited by jaslake; 05-07-2014 at 10:31 PM.

  82. #82
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    Edit: Once you know the upper left Cell Address of the Clicked Button you can use that address to perform other actions.
    Since the button is "hovering" over the cells, when you say Cell Address you are meaning the cell under which the upper left portion of the button is hovering over, right?

    I just got word from a user that they ran into a problem today when they entered the ITP Date mistakenly before they entered the Start of Care date on Sheet Demographics E13 & E14. I just tried to recreate the problem and they are correct. Once you enter a date in E14, "This review is late." check box134 is clicked automatically AND when the user goes back to enter the missing Start of Care date, check box134 remains checked and does not hide the range "LateNote".

    I'm off to try my hand at adding code to prevent the user from being able to enter a date into E14 if E13 is blank.

    Matthew

  83. #83
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Are you familiar with Break Points and the Immediate Window?

    We'll get back into this tomorrow...no, I don't mean this
    when you say Cell Address you are meaning the cell under which the upper left portion of the button is hovering over
    Let me know how you make out with the new Issue...

  84. #84
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    Hi Matthew

    Are you familiar with Break Points and the Immediate Window?
    Nope but I'll do some homework. Talk soon.

    Matthew

  85. #85
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Good morning, John. I arrived at a workable yet not exactly ideal solution for the Start of Care date entry dilemma.

    Here's the code I came up with:

    Please Login or Register  to view this content.
    I have tried to write the code so that if E13 was blank when a user attempted to enter a date into E14 an error message would pop up and not allow them to enter a date into E14 until a date was entered into E13. But alas I have reached my limits of understanding and my eyes are too heavy to continue. Talk soon.

    Matthew
    Attached Files Attached Files

  86. #86
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Good morning. I've tested the form with the most recent code changes and have found that the new code that I added in Sheet1 is bogging down my print feature. Would I be better off moving the newly revised code to another module so once the cells are filled the program won't run through those lines of code? Thanks.

    Matthew

  87. #87
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Please explain, in words, what this Code is attempting to do
    Please Login or Register  to view this content.

  88. #88
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    Hi Matthew

    Please explain, in words, what this Code is attempting to do
    Please Login or Register  to view this content.
    John,

    I chose this method primarily because of my inexperience than because it was an ideal situation. After submitting my post about the code bogging down the print function I realized that it was NOT this code that was bogging down the printing however I'm still dealing with another issue with the way I've written the code; i.e. the user may not know the start of care date at the time they go in to start an ITP for a client and if they do not enter a start of care date they will not be able to enter any other information in Sheet "Demographic".

    As I stated in a previous post, I have tried to write the code so that if E13 was blank when a user attempted to enter a date into E14 an error message would pop up and not allow them to enter a date into E14 until a date was entered into E13.

    If the code I came up with is the best for the job I'll stick with it. Thanks. Talk soon.

  89. #89
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    Best I can recommend for this
    if they do not enter a start of care date they will not be able to enter any other information in Sheet "Demographic
    is, if a Date is entered in E14 and E13 is blank, turn E13 Red and leave it Red until it's filled. The Code in the attached does that.

    The Code in the attached also addresses the printing issue you described in Post #68.

    Beat it up...
    Attached Files Attached Files

  90. #90
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I gave it a whirl and have decided to stick with my original option (for now) given that if the user enters the ITP Date BEFORE they enter the Start of Care date it throws a monkey wrench in the way Check Box 134 (and all the code that's associated with that "late note") operates. Thanks.

    BTW, I did some reading about Break Points and the Immediate Window and I found I'm more familiar with Break Points than Immediate Window. I need to do more to understand how to use the Immediate Window. Good stuff nonetheless. Thanks.

    Matthew

  91. #91
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I'll explain later why I asked about Breakpoints and the Immediate Window. Have Grandson duty for the next several hours.

  92. #92
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew
    Are you familiar with Break Points and the Immediate Window?

    We'll get back into this tomorrow...no, I don't mean this

    when you say Cell Address you are meaning the cell under which the upper left portion of the button is hovering over
    Open up VBA and go to Module 3, AddSTG_Click. Place a Break Point at the indicated line. Open the Immediate Window while you're in VBA (CTRL + g).

    Go back to Sheet ITP Goals & Obj...Click the Add STG Button. The Code will pause at the Break Point you placed in the Code. Go down to the Immediate Window and type in
    ?r.address and click Enter. The Immediate Window will display the current Address of the Button you clicked.

    To allow the Code to finish click F5 or terminate the Code.

    See picture attached. 2014-05-09_10-10-59.jpg

  93. #93
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    Quote Originally Posted by jaslake View Post
    The Immediate Window will display the current Address of the Button you clicked.
    Good morning, John. I followed your lead above (and repeated the steps multiple times) and here's what I got:

    ?r.address
    $C$20
    ?r.address
    $C$32
    ?r.address
    $C$44

    After adding the 3 goals I added by repeating the steps you laid out in post 92 I repeated the same steps except for the Delete STG button and this is what I got:

    ?r.address
    $D$56
    ?r.address
    $D$44
    ?r.address
    $D$32

    That's really cool. At least for me, given that I have the headings and gridlines turned off, this is very helpful to quickly find the cell that is associated with a command button. I'm guessing this is just the tip of the iceberg in regards to the many uses of the immediate window?

    Thanks for shedding some light on the topic.

    Matthew

  94. #94
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    Good evening. I've run into a snag with the CSR Template that I can't seem to overcome. Some of the rows in the template have merged cells and I need the text to wrap. I know we added code to Sheet Demographic to allow text to wrap in the rows that contained merged cells and I've attempted to replicate that code in Sheet9 "ITP Review" but can't seem to get it to work. Any chance you could give me a nudge in the correct direction on the code to allow the text to wrap on Sheet ITP Review? I really appreciate it. Thanks.

    The code I'm using on Sheet9 "ITP Review" is as follows:

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

  95. #95
    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: Syntax for referencing multiple ranges when using copy & paste feature

    Hi Matthew

    I'll be happy to look at this with you as the subject of a new Thread...PM me with a link.

  96. #96
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Syntax for referencing multiple ranges when using copy & paste feature

    John,

    I'm on it. Thanks.

    Matthew

+ 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] Copy ranges from multiple sheets and paste them in another workbook
    By LewisM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2014, 09:26 AM
  2. Macro to copy and paste ranges multiple times
    By MGuru in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2013, 02:16 PM
  3. Copy Paste Data across Workbooks with multiple sheets & Referencing.
    By v999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2012, 09:16 AM
  4. Copy, Paste multiple source ranges/destinations
    By JapanDave in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2011, 07:58 PM
  5. Copy paste ranges from multiple sheets
    By Woody1313 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2006, 11:40 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