Creating a new excel sheet via .Copy () and moving it to the last position

Question

I’ve been doing this for quite some time,

I want to create a new worksheet by copying another. This places a new sheet before copying the sheet. Then I want to move it to the very end of all the sheets.

The goal is to have worksheets called> summary1> summary2> summary3 .. etc. in that order

Here is what I have (sorry brevity)

$ex = New-Object -ComObject Excel.Application $ex.Visible = $true $wb = $ex.Workbooks.Add() for ($i = 1; $i -le 3; $i++) { $wb.Worksheets.Item(2).Copy($wb.Worksheets.Item(2)) $newSheet = $wb.Worksheets.Item(2) $newSheet.Activate() $name = "Summary$i" $newSheet.Name = $name $wb.Worksheets.Item($name).Move($wb.Worksheets.Item($i + 1)) } 

It works for the first sheet, it renames the sheet and moves it to the end, but after that, every time it reaches the .Move method, it does nothing but the .Activate () original sheet Summary.

I have no idea how to explain this behavior. Thanks, please in advance.

Edit: Changed $ wb.Worksheets.Item ("Summary"). Go to $ wb.Worksheets.Item ($ name) .Move

Edit:

Here is the solution:

 for ($i = 1; $i -le 3; $i++) { $wb.Worksheets.Item(2).Copy($wb.Worksheets.Item(2)) $newSheet = $wb.Worksheets.Item(2) $newSheet.Activate() $name = "Summary$i" $newSheet.Name = $name $lastSheet = $wb.WorkSheets.Item($wb.WorkSheets.Count) $newSheet.Move([System.Reflection.Missing]::Value, $lastSheet) } 

Answer 1

Marked as Community because this is from the question above.

Here is the solution:

 for ($i = 1; $i -le 3; $i++) { $wb.Worksheets.Item(2).Copy($wb.Worksheets.Item(2)) $newSheet = $wb.Worksheets.Item(2) $newSheet.Activate() $name = "Summary$i" $newSheet.Name = $name $lastSheet = $wb.WorkSheets.Item($wb.WorkSheets.Count) $newSheet.Move([System.Reflection.Missing]::Value, $lastSheet) } 

Answer 2

I can offer a more "general" and possibly faster answer, just for the sake of using it. Just keep in mind that this is from VBA and will require minor changes to run in the same environment as your question.

 Sub sheetCopier() Dim destSht As Object, srcSht As Object, NewShts As Long, shtCnt As Long Set srcSht = ThisWorkbook.Sheets(2) For NewShts = 1 To 5 shtCnt = ThisWorkbook.Sheets.Count srcSht.Copy after:=ThisWorkbook.Sheets(shtCnt) Set destSht = ThisWorkbook.Sheets(shtCnt + 1) destSht.Name = "New Sht" & Format(NewShts, "00") Next Set srcSht = Nothing Set destSht = Nothing End Sub 

obviously that might interest you:

 shtCnt = ThisWorkbook.Sheets.Count srcSht.Copy after:=ThisWorkbook.Sheets(shtCnt) Set destSht = ThisWorkbook.Sheets(shtCnt + 1) destSht.Name = "New Sht" & Format(NewShts, "00") 

and the rest just start the demonstration.

you can do things with even less code, but it's not so nice:

 thisworkbook.sheets(2).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = "New Sht" & Format(NewShts, "00") 

therefore, I assume (but cannot confirm) that your code can be written as:

 for ($i = 1; $i -le 3; $i++) { $wb.Worksheets(2).Copy(,$wb.Worksheets($wb.Worksheets.count)) $name = "Summary$i" $wb.Worksheets($wb.Worksheets.count).Name = $name } 

just note that the copy syntax is: .copy ([before], [after]) with options before and after. In vba, the following methods: .copy after:=ASheetObject or .copy ,aSheetObject

amuses