copy diff ranges into diff sheets based on a drop down list

[HansV]

There doesn't seem to be a real need to copy the column widths, since all ranges are from column A to column X, so you might as well set the column widths of all target sheets once.

But it can be done within the code. Change the part of the code that pastes the source range as follows:

Code: Select all

        ' Get info about current destination
        strDestination = wshList.Range("D" & lngIndex)
        Set wshDestination = Worksheets(strDestination)
        strDestination = wshList.Range("E" & lngIndex)
        Set rngDestination = wshDestination.Range(strDestination).Offset(lngCount * lngRowCount, 0)
        Set rngDestination = rngDestination.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
        ' Copy
        rngSource.Copy Destination:=rngDestination
        rngSource.Copy
        rngDestination.PasteSpecial Paste:=xlPasteColumnWidths
    End If

Note: the source range for Cmd_OP should probably be A11:X11 instead of A9:X9.

[vilas desai]

Thanks again and again...About the source range for Cmd_OP, I did notice it while testing and corrected it.

[vilas desai]

Hello Hans, Great! Simply Great!. It works excellent. Is there any place on this website to rate the expert and his efforts?
I have two follow-ups which I did not envisage accurately earlier. I tied with formulas, but no success since I guess only VBA codes could do the job, which is not my domain.
1) please see the attached, Sheet List!, table in Green color.
2) As the pages are getting populated due to the copy range, the code should keep a watch on
a) page break and then copy the range such that the page break does not divide the range into two parts in successive pages.
b) headers on every page (the first 4 rows / 5 rows as the case may be for different sheets), such that the page header does not divide the range into two parts in successive pages. This is shown in the sheet ControlValve! in the attachment.
C) FOOTERS on every page (the last 2 rows on every page), such that the page footer does not divide the range into two parts in successive pages. This is shown in the sheet ControlValve! in the attachment.

This help will be extremely useful in me moving forward with this assignment.

Appreciate your efforts, time and expertise.

Best regards
Vilas Desai

[HansV]

That looks quite complicated. I'll look at it later but I cannot promise anything, it might be beyond the scope of a discussion forum.

[vilas desai]

Dear Sir, Thank you for your reply. Before you spend any efforts I am trying to find some work-arounds and will come back to you. So please do no spend your precious time till I come back to you with any alternatives.
I guess Q1 was complicated, is there a solution which you could think of for Question2.
Best regards
Vilas Desai

[HansV]

The question about page breaks is complicated too.

I won't have time to look at it for the next hours anyway.

[HansV]

Both your problems can be solved, but it is far from easy, and in my opinion, it would take too more time than is reasonable for a discussion forum. But perhaps someone else is looking for a challenge...

[vilas desai]

Dear Hans, Thanks first of all for your courteous efforts and the great solutions that you have provided. I respect your opinion and also your time. When I began with this "self initiated" non commercial project, I did not visualize the complexity. Now that I have burnt a few midnight lamps into it, I would be lucky to see the light of the day. I am working for a company and thought by doing this code I would save my time and efforts. If an expert that you are suggests the complexity of the solution which cannot be resolved in a forum, then it's hard for me to think of any other expert who can solicit this challenge. But I would love to think of paying you for this effort out of my salary. May I request you to advise me how much hours of efforts and costs would be involved separately for the two questions. If i can afford it, I will work more intensely to make the object as clear as possible so that your time could be saved. Thanks again and with best regards, Vilas Desai

[HansV]

Thanks for your kind words. I have a daytime job, and between that and my being active as a volunteer here and in the Microsoft Answers forums, I am not available for freelance work.

[vilas desai]

OK, no problems. All that I can say, is when ever you find any time for this and get me a solution, I would be very appreciative. Meanwhile, please let me know if you know of any expert who can get me a solution either as a free-lancer or as a forum moderator.
Regards
Vilas Desai