+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 201 to 235 of 235

Copying random columns from one sheet to another in same workbook..Problem?

  1. #201
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Post # 198: I am not sure why I need it. But I will take a closer look. You could be 100% correct!

    Post # 199: Well I have figured some out and add a couple things that worked but since it works I don't really need to understand and I am figuring out a little more as I work with it. So alls good!

    Post # 200: Sounds good! I will add a button for Property Blocks to frmStayEntry_Tisjallie

    Thanks

    u3rick
    Last edited by u3rick; 09-14-2015 at 11:47 AM.

  2. #202
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Should I change all the Formulas in the tables sheet columns to INDEX MATCH formulas?

    u3rick

  3. #203
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Try adding a new Unit like use xy JL 604 as PropertyCode and fill the rest in with whatever. Question is why is it so slow after you click Enter Unit?

    u3rick
    Last edited by u3rick; 09-14-2015 at 02:21 AM.

  4. #204
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Post 198: You were correct as usually!

    u3rick

  5. #205
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Should I change all the Formulas in the tables sheet columns to INDEX MATCH formulas?
    If the formulas work ok, then there's no need to change them.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  6. #206
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Try adding a new Unit like use xy JL 604 as PropertyCode and fill the rest in with whatever. Question is why is it so slow after you click Enter Unit?
    There is a formula in the Properties table. This makes inserting and add values to it starting a recalculation.
    If you set calculation to manual when the cmdSaveEU_Click starts and set it to back to automatic again at the end it will run faster.

  7. #207
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Can you give me a hint on how that might look?

    u3rick

  8. #208
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Do you mean setting calculation to manual?

    Please Login or Register  to view this content.
    Last edited by Tsjallie; 09-14-2015 at 04:37 PM.

  9. #209
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Yes and Thanks

    u3rick

  10. #210
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Added to code to my post.

  11. #211
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    That speeded it up a bit!

    Thanks!

    u3rick
    Last edited by u3rick; 09-14-2015 at 06:48 PM.

  12. #212
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    I need a little help on my owner and Property edit form. The first tab works fine and I need the help on the second tab

    Tab one:

    Screen Shot 2015-09-14 at 3.12.00 PM.png

    Tab two:

    Screen Shot 2015-09-14 at 3.14.56 PM.png

    The green outlined box is a listbox and it should list all the properties owned by the owner picked in tab one.

    The the property or properties in the listbox can be clicked on which loads the rest of the information on the tab under the listbox.



    Thanks

    u3rick
    Attached Files Attached Files
    Last edited by u3rick; 09-15-2015 at 02:50 PM.

  13. #213
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    I was doing some testing with locking the Calendar. I noticed that when highlighting a number of vacant months and right clicking, that the highlighted months snap back to the first month. Is this something I did or something that came with the latest version of the Calendar that you changed? I am sending you the version that I am using right now and will make no changes till I hear from you.

    u3rick

    ES Program Redesign_Ricks_v2.4b.xlsm
    Last edited by u3rick; 09-15-2015 at 04:44 PM.

  14. #214
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    That came with on of the latest versions. Was intended to force/aid selecting valid dates, but need to do that thru the datepicker.
    That's still inthe pipeline.
    So still you haven't been wrecking anything (apart from some boats of course);-)

  15. #215
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    If you want to show all the properties owned by a selected owner then you would need a list(box). The form as it is now will only show one property.
    BTW, looking into the Property Availability and integrating it into the workbook, I think the Owners and Properties form need some redesign to better integrate with each other.
    Together with de Property Availability I will come up with a suggestion for that in by - I think - next weekend. OK?

  16. #216
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    That is fine I am racing this weekend (Leaving tomorrow back Monday, of course I will have my computer if needed)!

    That green box is a listbox. I have listboxes in other forms, but can't remember or locate the code to make the units showup and fill the form below??

    Thanks

    u3rick
    Last edited by u3rick; 09-15-2015 at 05:18 PM.

  17. #217
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    I finally figured out post # 212 and the form is working the way I want.

    u3rick

  18. #218
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    I have the Owner and Property edit form working. If you go to the Calendar and click the edit button and pick any owner but Sproston it works. Sproston is the only owner that has two properties. if you click on him it errors. Can't figure out why but it has something to do with the second property??

    By the way you have to double click property in the listbox!

    Can you check this and see why it errors?

    Thanks

    u3rick

    ES Program Redesign_Ricks_v2.4c.xlsm

  19. #219
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Mr. Sprostron has two properties. That means that the rowsource spans two rows.
    Problem is that these rows are not adjacent and rowsource can't handle that.
    To solve this you need to sort the table first. As this is not the real table (it's a view) you'll need to sort the source table (PropertyOwner) on OwnerId, PropertyId and Status.

  20. #220
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    I will give that a try.... figured it was something like that!👍

    Thanks

    u3rick

  21. #221
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Tried on the plane but may need a little mapping where i change that info above!

    u3rick

  22. #222
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Better wait for my changes. Need an extra day (or maybe two) to finish them properly.

  23. #223
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629
    Quote Originally Posted by Tsjallie View Post
    Better wait for my changes. Need an extra day (or maybe two) to finish them properly.
    Thats fine take you time!😀

  24. #224
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Question; why do the tables I built work with the Guest Actives form, when bring up varied data in a listbox? Then in the same type of use it won't work bring up two units for the same owner in a list box??

    Thanks

    u3rick

  25. #225
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Made everything work that I couldn't in post 218. The list box now shows more than one unit! So disregard the help I requested in that area!

    Thanks

    u3rick

  26. #226
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Hi Rick,
    it took some time. Had some bad luck.
    Here is a new version with a userform integrating property details, property availability and owner details.
    Choose for a userform with multipages to make exchanging id's between tabs easier and have all the code nicely together.
    Some things need to be done yet. Specially saving the data.
    I couldn't decide on having the data save all together thru a save-button or directly when something changes.
    The first option maybe slow like with saving a new unit. The other however may be confusing.
    I'll leave these last bits to you to do. So you can decide what you like best. And it'll keep you off the street
    To do list is on the calendar sheet.

    Right-clicking on the PropertyCode in the calendar will bring up the form. Rest will show itself.

    I also made some changes to the formula in the occupation cells in the calendar to handle unavailability periods.
    A unit can be permanently unavailable (column Active in Property table = "No"), regularly unavailable (e.g. july and august) or ad hoc unavailable (specific periods).
    Unavailability shows up in the calendar as "N/A", unless the unit is occupied. Then it will still show the stay data, because you shouldn't want occupations to remain hidden.

    Last but not least the calendar is now showing all units, but permanently unavailable units are show with property code stroked through and can't be booked.
    So no more need to keep the property table sorted on active+propid, just on propid. I noticed that a lot of lookups gave incorrect results. Replacing all these lookups with index/match would mean quite some work. Would be recommended though, but only when you start redeveloping the app all over (yes, you will some moment in time ).

    Questions? Don't hesitate to ask.
    Other ideas? Don't hesitate to change anything you want.
    Don't like it at all? Just throw the whole thing away (but bring the message gently )
    Attached Files Attached Files
    Last edited by Tsjallie; 09-23-2015 at 04:06 PM.

  27. #227
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    wow this thread is now 16 pages long, and 226 (so far) posts, and been going on for over 3 months.

    Is this still all related to the same question? (I have not been following the discussion)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  28. #228
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Thanks.... I will check it out. I am sure it will be good!

    I will let you answer FDibbins. Ok?

    Thanks for all the effort!

    u3rick

  29. #229
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Hi Ford,
    I have been thinking back of how this thread started (telepathy?).
    It indeed has nothing to do with the original questions anymore; we've got "a bit" carried away I guess. :0
    So may be time to change that.
    @Rick, can you think of a nice new thread name that covers the content?

  30. #230
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    What about this? "Populating froms with new or existing data and saving to data sheets".

    u3rick

  31. #231
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Sounds good to me, thanks

    It can be debated whether the OP can hijack their own thread, but.................... LOL

  32. #232
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2013, 2016, Mac 2016
    Posts
    629

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    Tsjallie

    Made new thread "Populating froms with new or existing data and saving to data sheets".

    We will continue there!

    u3rick

  33. #233
    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: Copying random columns from one sheet to another in same workbook..Problem?

    @Ford

    I had one, back in the early days, that was up to (as I recall) 238 Posts. I have been looking in on this one periodically simply because of the Post Count.
    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.

  34. #234
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copying random columns from one sheet to another in same workbook..Problem?

    John - all about the same initial question?

  35. #235
    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: Copying random columns from one sheet to another in same workbook..Problem?

    @Ford

    I believe related (or closely) to the initial issue. The OP and I, obviously, were not communicating well...language barrier.

    Back in those days I simply didn't give up. To the OP's credit, neither did he/she.

    With the referenced Thread and others, Boro183 (Rob) became, I consider, a Mentor to me as have others. Haven't seen Rob around in a while...it's too bad. Rob was very good to us neophytes.

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Random List Generator How to copy Columns A and B to Sheet 2
    By guitarsweety in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-08-2014, 09:01 PM
  2. [SOLVED] Copying data from one workbook sheet to another workbook sheet without overwrriting
    By Amol1988 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-09-2014, 01:58 AM
  3. Copying unprotected data from one sheet of workbook to another workbook
    By rushabh bhansali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2013, 06:18 AM
  4. Replies: 4
    Last Post: 12-22-2012, 01:41 PM
  5. Append Data From One Workbook to Another Workbook (Consolidate Random Columns)
    By capnhud in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-23-2010, 01:15 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