• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help thinking through a conditional copy/paste issue in a VBA

O

OleMiss2010

Member
I have a VBA that asks a series of questions to create a master list of issues that are either "cleared" or "open". Once all of the issues are added I want the VBA to finish by going through the master sheet and putting a copy of all cleared issues in a tab and putting a copy of all open issues in another tab.


A few pieces of information.

1) The master list is a running list, so the VBA does not create a new master list each time but rather adds to the list.


2) Each issue has 8 column and 4 rows associated with it. 6 of the eight columns have all 4 rows merged into one cell but 2 columns use all four rows.


Any ideas on how I should go about accomplishing my goal?
 
6 of the eight columns have all 4 rows merged into one cell but 2 columns use all four rows.
Click to expand...

Merged cells can cause some major headaches when using VB...any chance at all this can be avoided?


Ignoring that issue for now...

Assuming there's some column in the master sheet, could you do some sort of AutoFilter/AdvancedFilter and then copy results to appropriate sheet? This would avoid a VBA loop, and should keep the speed relatively constant.
 
I knew the merged cells would be an issue. That's the reason I included that fact. I liked your filter idea, but the merged cells actually caused a problem in getting that information to copy. My solution for now is just to insert a filter at the end of the VBA and the user can then filter the results between cleared and open as the choose.
 
Well, didn't hurt to ask. =P


In my workplace, I was only able to get around the merged cell feature by unmerging all the cells, doing any filtering/sorting that was needed (although sorting gets tricky, as you need to fill in the unmerged blank cells...), copy the data over, and then use some type of loop to go back through an remerge everything. Loop would check for every 4th row. Sound possible?
 
I thought about that as an option as well. The main concern I have there is that when it unmerges three cells in the 6 columns that are merged will be blank. So I would need to copy their contents in then do the filter, then delete the contents out of the three of the cells then remerge. It's clearly possible, but it might be more work than it is worth. I'll see how things go with the simple filter on the master sheet. Thanks for the help though.
 
You must log in or register to reply here.
Back
Top