Multiple Named Ranges Pasting Loop issue

Hi Guys,

I'm pasting values from one sheet to another based on multiple named ranges. Woks great when pasting one column but when i add multiple ranges, UFFF!...
Read below...

General Overview:

I have a sample workbook attached, there are (2) sheets of importance.

First is the sheet (EstSummary) where I have miraculously been able to hack together coding a button control to import data from the (Svc) sheet.

The second Sheet (Svc) is a table of estimating service data that i have put together to input and price services.

I have created a number of named ranges (SvcDesc, SvcHdr, SvcQty, SvcSid, etc..)..
All the ranges above are single columns in the the Svc table and are dynamic leveraging the OFFSET function.

First issue: (Code in the import button.)

When I run this code to copy a single named range to the EstSummary Sheet, i have no issues pasting and re-pasting as the code checks for text and offsets one row past the next available cell row in that column.

When I augment the code to reference multiple named ranges, the first paste works great. But then if i hit the button again, it freezes in the loop and i have to force the app to close. I will be duplicating this button to pull from additional sheets like the (Svc) sheet so I am looking to fix the loop. I am JUST learning VB so I'm still very basic with my understanding.

Start CODE:======================================================

Please Login or Register  to view this content.

End Code==========================================================

Second issueBlanks in the named range)

In My SvcHdr named range there are sometimes blanks.

The range should extend from C13:C55.. I use the header information for conditional formatting on both pages (H1 for example). So I may have it on C13 and again on C20, then C30.

The problem is that when there are blanks in between the rows the dynamic range extends only to the second occurance of the H1 value.

For instance, if I look at the range as it is set up above, it will show me that the range spans C13:C20.. It will not go past C20 to also cover C30 and down the rest of the column down to C55...

I guess i could work around this by setting a sheet macro that will look at that range and fill that column with "-" or something so that there is a value in the field. I tried to but i couldn't get it to work.

Any help would be appreciated.

Thanks,

P

Good morning pacman

Originally Posted by pacman_d

Am i doing something wrong in my posts? Is there something that i am not doing or a rule that I am not following here? I guess i will give Mr Excel a shot.

Absolutely. The code tags (see below). Just a hint, but posting to MrExcel without code tags will result in your post being ignored, but no-one is likely to mention it. As stated below code without tags makes it harder to read.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

DominicB

And don't forget to post links to your cross-posts in other boards...

X-post here.

I wasn't referring you back there - it is a forum rule to provide a link if you cross-post in other forums (as well as being net etiquette). It saves people suggesting things you have tried elsewhere or working on something that has already been solved in another forum.