Adding a worksheet within the workbook at specific position
 

hi Guys,
Need Help. I am writing a macro to add a worksheet within a workbook and
named it "DrListWorkCopy". I then copy a range of cells from Worksheet
"DrList" into it.

How do I dictate that "DrListWorkCopy" is incerted right after worksheet
"Targets" within the workbook?

I tried:
Set NewWks = Worksheets.Add After:=sheets("Targets")

but got a syntax error.

Here is how I coded the whole procedu

Sub DuplicateDrList()

Dim NewWks As Worksheet
Dim SourceWks As Worksheet

Set SourceWks = Sheets("DrList")
Set NewWks = Worksheets.Add
NewWks.Name = "DrListWorkCopy"
With SourceWks
.Range("A15:AJ" & SourceWks.Range("AJ65536").End(xlUp).Row).Copy
Sheets("DrListWorkCopy").Paste
End With
End Sub

Adding a worksheet within the workbook at specific position
 

Hi

This should do it:

Set NewWks = Worksheets.Add(after:=Sheets("Target"))

Regards,
Per

On 29 Okt., 05:15, Blubber wrote:
hi Guys,
Need Help. I am writing a macro to add a worksheet within a workbook and
named it "DrListWorkCopy". I then copy a range of cells from Worksheet
"DrList" into it.

How do I dictate that "DrListWorkCopy" is incerted right after worksheet
"Targets" within the workbook?

I tried:
Set NewWks = Worksheets.Add *After:=sheets("Targets")

but got a syntax error.

Here is how I coded the whole procedu

Sub DuplicateDrList()

*Dim NewWks As Worksheet
*Dim SourceWks As Worksheet

Set SourceWks = Sheets("DrList")
Set NewWks = Worksheets.Add
NewWks.Name = "DrListWorkCopy"
*With SourceWks
*.Range("A15:AJ" & SourceWks.Range("AJ65536").End(xlUp).Row).Copy
* Sheets("DrListWorkCopy").Paste
End With
End Sub

Adding a worksheet within the workbook at specific position
 

Hey Thanks Per Jessen. It worked perfectly.

"Per Jessen" wrote:

Hi

This should do it:

Set NewWks = Worksheets.Add(after:=Sheets("Target"))

Regards,
Per

On 29 Okt., 05:15, Blubber wrote:
hi Guys,
Need Help. I am writing a macro to add a worksheet within a workbook and
named it "DrListWorkCopy". I then copy a range of cells from Worksheet
"DrList" into it.

How do I dictate that "DrListWorkCopy" is incerted right after worksheet
"Targets" within the workbook?

I tried:
Set NewWks = Worksheets.Add After:=sheets("Targets")

but got a syntax error.

Here is how I coded the whole procedu

Sub DuplicateDrList()

Dim NewWks As Worksheet
Dim SourceWks As Worksheet

Set SourceWks = Sheets("DrList")
Set NewWks = Worksheets.Add
NewWks.Name = "DrListWorkCopy"
With SourceWks
.Range("A15:AJ" & SourceWks.Range("AJ65536").End(xlUp).Row).Copy
Sheets("DrListWorkCopy").Paste
End With
End Sub