Copy Template Sheet

[paulsolar]

Hi All

I've been trying for an hour now and cant find a way of doing this.

I have a worksheet called "Sales Manager" in column A rows 1-19 I have a list of their name. Currently only rows 1-8 have names in them. I'm trying to make this as dynamic as possible to future proof it.

Each sales manager has their worksheet already being created dynamically from the list.

What I want to do is copy a worksheet named "Template" to each of their sheets. I know that I can copy the Template sheet when I create the new sheet, but I dont want to do it this way if possible.

Could some kind soul please help out this.

I've been at it for an hour now and going around in circles.

Cheers

Paul

 

[rlv01]

What I want to do is copy a worksheet named "Template" to each of their sheets

What does that mean? Your description of what you want to do is confusing.

 

[paulsolar]

Sorry it it was a bit confusing.

I have a worksheet for each sales manager already created (which are their names). I also have a worksheet called Template.

What I want to do is copy the Template sheet to each of the sales managers sheets.

I have a list of the sales manager names in a sheet "Sales Managers" in column A rows 1-19. Currently there are only 8 sales manager but the number of sales manager can go up and down.

So what I require is to loop through the sales manager names in sheet "Sales Managers" in column A rows 1-19, and copy the Template sheet to each of the sales managers sheets that are in that range.

I'm really struggling with this because not all the cells in the range "Sales Managers" in column A rows 1-19 have data in them (although the empty cells are always at the end of the range). Currently I have it hard coded and working, but the sales manager change frequently and I have to keep changing the macro.

I hope this is a better explanation.

cheers

Paul

 

[rlv01]

Ok. A bit better, but a little more may be needed. Let's say you have a sales manager named "Thurston". You have said that you want to copy worksheet "Template" to worksheet "Thurston", and you have implied that Template and Thurston are in the same workbook. The problem is that you cannot copy one entire worksheet to another entire worksheet. You can copy the worksheet Template and use it to replace Thurston (after first deleting Thurston) but you cannot copy it "to" Thurston.

You can however copy some portion of the cells in worksheet "Template" to some cell address you define in worksheet "Thurston" - but those are not the words you used to describe what you wanted to do.

 

[paulsolar]

Ok. A bit better, but a little more may be needed. Let's say you have a sales manager named "Thurston". You have said that you want to copy worksheet "Template" to worksheet "Thurston", and you have implied that Template and Thurston are in the same workbook. The problem is that you cannot copy one entire worksheet to another entire worksheet. You can copy the worksheet Template and use it to replace Thurston (after first deleting Thurston) but you cannot copy it "to" Thurston.

You can however copy some portion of the cells in worksheet "Template" to some cell address you define in worksheet "Thurston" - but those are not the words you used to describe what you wanted to do.

Hi

Ahhhh!!!! now i can see why I was having an issue

Can you just copy the used range, I'm not sure if this is possible?? If not then I would need to copy Range A1: FA10000 from the template to paste in the sales managers sheet. it would be a straight copy as I need the formatting and formulae.

I hope that makes more sense now

thanks for your help with this.

Kind regards

Paul

 

[rlv01]

There is a worksheet property called UsedRange. You can use it to copy, e.g.

ThisWorkbook.Worksheets("Template").UsedRange.Copy
ThisWorkbook.Worksheets("Thurston").Range("A1").PasteSpecial xlPasteValues

 

[paulsolar]

Brilliant thanks, I know where i was going wrong now