CODEWITHSUNDEEP
excelvbaexport-to-csv
sbit
sbit

Reputation: 1

Deleting top row of every sheet on a copied workbook

I'm saving a closed workbook in individual sheets as .csv files for opening in minitab. The code to save each individual sheet as .csv works great. The issue is I also need to delete the top row of each sheet before saving it as .csv without affecting the original file.

The code will work on the first iteration and the .csv file for the first sheet is correct. But once I hit the second loop, I get errors on the code line to delete the top row.

The errors are all listed in the code below next to the variations I've tried.

Dim objExcel
Dim objWB
Dim objws
Dim myPath As String
Dim i As Integer
Dim row As Integer
Dim col As Integer

Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("file location")

    'prevent overwrite warnings
    Application.DisplayAlerts = False
    
i = 1
For Each objws In objWB.Worksheets
  
  myPath = "\\G:\file location"
    
    objExcel.ActiveWorkbook.Worksheets(i).Copy
    'objExcel.ActiveWorkbook.Worksheets(i).Activate 'if this segment of code is active, it also gets Sub script out of range on second loop

    objExcel.ActiveWorkbook.Worksheets(i).Range("a1").EntireRow.Delete 'run-time error 9 Sub script out of range on second loop
    'objExcel.ActiveWorkbook.Worksheets(1).Range("a1").EntireRow.Delete 'run-time error 1004 Delete method of range class failed on second loop
    'objExcel.ActiveWorkbook.objws.Range("a1").EntireRow.Delete 'error object doesn't support this property or method

    objExcel.ActiveWorkbook.SaveAs Filename:=myPath & objws.Name, FileFormat:=xlCSV, CreateBackup:=False
    objExcel.ActiveWorkbook.Close False
    
    i = i + 1
 
Next

    objExcel.ActiveWorkbook.Close True
    Application.DisplayAlerts = True

Upvotes: 0

Views: 42

Answers (1)

BigBen
BigBen

Reputation: 49998

Likely two issues:

  1. objExcel.ActiveWorkbook.Worksheets(i).Copy should be objws.Copy.
  2. Then, objExcel.ActiveWorkbook.Worksheets(1).Rows(1).Delete to delete the first row in the copied worksheet.

Note that i is likely superfluous.

Upvotes: 1

Related Questions