Search code examples
excelvbafor-loopcopysave-as

copy lots of sheets and delete a few sheets

I was wondering if someone could help me with the code. I am almost there but not fully.

Sub col_export_Klikken()

Dim wb As Workbook
countsheets= ThisWorkbook.Sheets.Count

Set wb  = Workbooks.Add
 For nsheets = 1 To countsheets
  ThisWorkbook.Sheets("sheetname").Copy After:=wb.Sheets(1)
 Next nsheets
ThisWorkbook.Sheets("mainuser").delete
ThisWorkbook.Sheets("maincode").delete

wb.SaveAs "C:\Users\not\Desktop\wb2.xlsx"
End Sub

This code doesn't work yet. But I hope with your help I can figure out how I can save sheets in a file and delete a few sheets. First problem I am facing is using a sheetname within quotes. This is very static and the sheetname will change continue. Everytime I generate a code I get lots of worksheets.

I want to have this code to work like it copies all the sheets in the workbook. After that it will delete a few sheetnames (these sheets are static so this can be done easily).

Solution

  • If you are just wanting to copy every sheet you can simply use:

    ThisWorkbook.Sheets.Copy after:=wb.Sheets(1)

    therefore not having to count the sheets and loop through them. Alternatively, if you want to loop through each sheet so you can run some checks to test that you do want to copy each one, you could change your reference to:

    ThisWorkbook.Sheets(nsheets).Copy After:=wb.Sheets(1)