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 procedure:

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

Hi

This should do it:

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

Regards,
Per

On 29 Okt., 05:15, Blubber <Blub...@discussions.microsoft.com> 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 procedure:
>
> 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