Select Sheet Paste on last

[ArturS75]

Hi,

I got this code

LastSheet = Sheets.Count
Sheets("Template").Copy After:=Sheets(LastSheet)

Copies All template sheet and creates sheet and paste the content.

I cannot work out how to do the same but copy only a range of cells in Template?

 

[Joe4]

Welcome to the Board!

Something like this:

'   Insert new sheet at end
    Dim sht As Worksheet
    Set sht = Sheets.Add(After:=Sheets(Sheets.Count))
    
'   Copy range from range A1:B2 of "Template" sheet to cell A1 of new sheet
    Sheets("Template").Range("A1:B2").Copy sht.Range("A1")

 

[ArturS75]

Hello,

That did the trick. Thank you.
The previous code (the first one) Added a count to the new created sheet.
So it would be Template1, Template 2, Template 3, etc.

So, what I want to do is
copy range of cells from "Template"
Paste that range into new sheet which creates and adds a count Template1 then Template 2. Is Template 2 created then it creates Template3.

 

[Joe4]

You could do this:

'   Insert new sheet at end
    Dim sht As Worksheet
    Set sht = Sheets.Add(After:=Sheets(Sheets.Count))
    sht.Name = "Template" & Sheets.Count + 1
    
'   Copy range from range A1:B2 of "Template" sheet to cell A1 of new sheet
    Sheets("Template").Range("A1:B2").Copy sht.Range("A1")

 

[ArturS75]

Hello,

Thanks, I think that do the trick. I just complecate things too much with my code. I just dont get it why the count starts at 4
Template and then it creates Template4

 

[Joe4]

Hello,

Thanks, I think that do the trick. I just complecate things too much with my code. I just dont get it why the count starts at 4
Template and then it creates Template4

Because it is the 4th sheet in your work book.
"Sheets.Count" does not just count the number of sheets named "Template", it counts the total number of sheets in the workbook.
If you always have 2 sheets in your workbook not named "Template...", you can just change this line:

    sht.Name = "Template" & Sheets.Count + 1

to this:

    sht.Name = "Template" & Sheets.Count - 1

to adjust your count down 2.