Edit code to retain column width when pasted
The below finds, copies and pastes data into a new sheet but NOT with the same column widths. I'm really rusty w/ my VBA and need help adding to this code to make it RETAIN the column width parameters it sees on the source sheet (Agile_Capture_Log) when pasting into the empty sheet (Output_Report).
THANKS so much! Working over the holiday to have this ready for boss by Mon.
Chris
Please Login or Register to view this content.
Last edited by CHRISOK; 09-06-2009 at 01:21 PM. Reason: forgot to add code tags
Hello CHRISOK,
Welcome to the Forum!
Here is how to do it...
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Re: Edit code to retain column width when pasted
Beautiful! Thanks SO MUCH! You're awesome! I'm SO excited!! (attaching sample)
While I was awaiting a reply, I realized I would also need to bring over the header row of the Source page and have the OUTPUT pg cleaned before the paste occurs each time the user initiates the macro...and have been looking for code to facilitate... (otherwise, every time it pastes data it will be be a mess)
The only other problem w/ this is that a user can't change their criteria (it is currently hard-coded to only locate "BP1" line items and copy them over to the OUTPUT PG - but they will want to have the ability to pick from the validation list (E3:E35 of the 'DROPDOWNS' sheet). If you can direct me to code that can help resolve those last 2 issues, I'd GREATLY appreciate it... (attaching sample)
Hello CHRISOK,
The user can select the report criteria from CombBox on the form. This loaded using the named range "AppliesTo". The report sheet is cleared each time leaving the header row intact. Here is the UserForm1 code. The attached workbook has all the changes made to it.
Please Login or Register to view this content.
Hello CHRISOK,
I found some problems and have corrected them. Use this version instead of the other. The corrected workbook is attached.
Please Login or Register to view this content.
Re: Edit code to retain column width when pasted
Wow Leith, your updated attachment is very kool!
I was so excited to see it working!
I didn't realize I had posted the file that had some sample forms in it that I was going to try to re-use...but decided the one UserForm1 will be sufficient to allow them to select 2 criteria for now....Column D and Column H. (I tried to ADD TO what you had (to include the ComboBox 2 and Column H stuff)- but, I've forgotten so much - I know I've got the format messed up --- probably needs to be some close/end statements in there to make it work correctly.
I'm just SICK that I've forgotten so much! I used to be the "office wiz" at this and created all kinds of kool things - but job changed to primarily tech writing, software analyst and project mgmt that my VBA skills are shot! I am now inspired to make it a rule to create something small once a month to RETAIN!
The last thing I noticed was, after the user selects their ComboBox criteria and hits "Create Report" - then, I'd like to 'show' that Output_Report 'Sheet5'.
After that, they'll probably want to change the criteria from something like "BP1" items to "BP2" items so, I added an image on the main spreadsheet for them to be able onclick it and call/show the UserForm1 again to be able to Re-Run the report w/ different criteria.
I googled "call / show UserForm from image" - but nothing seems to match up to what I'm trying to do.... any ideas? (updated file attch'd)
Hello CHRISOK,
To open the UserForm when the icon is clicked requires a macro to show the UserForm and then attach that macro to the icon.
You can attach the macro by Right-Clicking the icon (Shape Object) and select "Assign macro...". Choose "Run" from the list and click "OK".Please Login or Register to view this content.
As for the change in criteria, I am not sure I follow you. The ComboBox on the form allows the user to select the criteria to use for the report. Can you give me some details on this new approach is to work?
Re: Edit code to retain column width when pasted
Thanks! I got the macro working. As you can see from the file posted yesterday, I had indeed created a macro w/ show references - but fixed it accordingly w/ what you presented and it works perfectly now! THANKS!
However, the other stuff I tried is causing issues (understandibly)
Ok, here's the anticpated steps for User:
Open file, dialog box opens providing them 2 criteria buttons (rather than 1).
The top one has them select which BlockPoint they wish to filter down to..
The 2nd combo has them select which Status they wish (i.e. "Pending").
Originally, I was going to try to use 2 forms as done before...
#1- A welcome
#2- A form to allow them to select multiple criteria (any of the columns)
#3- Once they hit "Generate Report" it would take them to the results page (OUTPUT)
(they can run their mtg using this, or print as desired)
#4- Then, if they want to change the criteria (I provided that icon/image to call back up the criteria combo buttons to allow them to select a different Blockpoint (like "BP2") and perhaps only approved items ("yes") rather than ("Pending).
However, -- there's really no need for 2 forms -- when the buttons can appear on UserForm1 as it is currently. (so, I have combined #1 & 2)
Only thing left is to make #3 & 4 work.
Since the boss needs it for today -
I can add to it later (add the ability to choose from any of the columns using multiple buttons for multiple critera filtering) but for now, those 2 combo buttons of criteria should get them going for the agile development tracking mtgs.
Thanks again, I'm really excited to review and get back into the swing of using the code! I REALLY appreciate your guidance! Chris
Last edited by CHRISOK; 09-08-2009 at 10:48 AM. Reason: forgot to clarify a part
Nevermind - very happy to report I got it!
I found the parameter to adjust -- :D
Changed the following from 1 to 2 and the other from 2 to 3 and it works great!
OutRng.Offset(2, 0).ClearContents
Set OutRng = OutRng.Cells(3, 1)
Thanks again!
Chris, please edit your post to add code tags.
Thanks.
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks