Set values only of range of previous sheet equal to range of original sheet

[ozizushi]

I'm trying to make some code more efficient. What I need to do is duplicate an entire sheet and then replace one of the ranges with values only.

One of the problems I have is that I want to avoid referring to the new sheet by name since I need to repeat this action and each new sheet will have a new name. I'm sure there is a way to control the new sheet name so that it can be referred to but I prefer not to do so since it would probably complicate things.

Here is my original code. Don't read it, it's just here for posterity.

Please Login or Register  to view this content.

I think I can distill all of this down to two lines. Here is the first thing that I tried:

Please Login or Register  to view this content.

I have two problems with this. It seems to be quite a bit slower and it won't let me do .Value.

I like this method better but the VBE doesn't like my use of "Previous":

Please Login or Register  to view this content.

I like the idea of getting this down to two lines of code but more important to me is making it fast and minimizing flickering or screen redraw.

Help is appreciated. Thank you for reading!

[Greg M]

Hi there,

The following code should do what you want:

Please Login or Register  to view this content.

You could change your own code to:

Please Login or Register  to view this content.

That will also work, but ActiveSheet is a generic Object and therefore IntelliSense cannot display its Methods or Properties. Declaring wksNewSheet as a WorkSheet object means that IntelliSense is enabled for it.

Note also that if you ever need to change your worksheet and range names, it will be necessary to make five changes to your code - declaring these names as Constants means that only two changes will be required

Hope the above helps - please let me know how you get on with it.

Regards,

Greg M

[ozizushi]

Thanks Greg! Unfortunately it is beer o' clock over here and I won't be looking at this again until Monday morning (I've caused some other problems in my workbook that need attention too). Once I've killed off all the weak brain cells and fortified the strong ones I'll be putting your advice into action. Thanks for the tips on the constants too. I have much to learn.

[Greg M]

Hi again,

Many thanks for your prompt response despite the fact that the local tavern was calling out to you for assistance

I hope my previous post will be helpful to you, but if not, just ask. I'll be away from my pc for a while, so if you don't get any replies from me it won't be because I'm ignoring you!

I'll check this thread when I'm back in action and I'll reply to any further questions at that stage.

In the meantime, enjoy those beers

Regards,

Greg M

[ozizushi]

I'm having trouble testing anything today. Keyboard shortcuts seemed to have stopped working. The macros seem to work okay if I run them directly but sometimes I get errors. The errors are not consistent either. Sometimes I get an error and sometimes I don't.

As of right now, whatever script I run errs during the copy worksheet part (Method 'Copy' of object '_Worksheet' failed). Aaaaaaannnnnnd Excel just crashed.

Earlier, I was able to get through the copy part but then get an error on this line:

Please Login or Register  to view this content.

When stepping in I get: Compile Error: Method or data member not found. The first line, "Sub CopyTemplateWorksheet() is highlighted in yellow but The ".Range" in the last line of code is highlighted in blue.

I have two other modules that might be contributing to my problems. I think I may need to start a new thread just to show the big picture.

[ozizushi]

I fixed my other problems and your solution worked great. Thanks Greg!