Delete first 9 rows of data

[WilliamA]

Hello,

I have some VBA code which copies information from one spreadsheet to another. How can I delete the first 9 rows of the data that gets pasted to the new spreadsheet? I have tried adding this line of code but it doesn't work. Am I missing some sort of linking element?

Rows(9).EntireRow.Delete

This is the working code

Sub copy_data()

Dim data_wb As Workbook
Dim target_wb As Workbook
Dim file_name As Variant
Dim header_range(100) As Range
Dim last_row As Long
Dim col_number As Long
Dim col_letter As String
Dim counter As Long
Dim quantity As Long

'select workbook
file_name = Application.GetOpenFilename(Title:="Choose a target Workbook")

If file_name <> False Then

'create a new target workbook
Set target_wb = Application.Workbooks.Add

'open workbook with the data
Set data_wb = Application.Workbooks.Open(file_name)
    
'get quantity to create loop
quantity = _
InputBox("How many columns do you want to copy?")
    
'loop
For counter = 1 To quantity
    
'select header range
Set header_range(counter) = _
Application.InputBox("Select the HEADER of the " & counter & "º column you want to copy", Type:=8)
        
'get last row and column letter
col_number = header_range(counter).Column
last_row = Cells(Rows.Count, col_number).End(xlUp).Row
col_letter = Split(Cells(1, col_number).Address(True, False), "$")(0)

'copy from original workbook
Range(header_range(counter), Range(col_letter & last_row)).Copy
        
'paste in target workbook
target_wb.Sheets("Sheet1").Cells(1, counter).PasteSpecial xlPasteValues
        
'delete the first nine rows
Rows(9).EntireRow.Delete
        
 Next counter
    
data_wb.Close
    
'prompt user to save the file
If Not target_wb.Saved Then
If MsgBox("Do you want to save the file?", vbYesNo, "Save?") = vbYes Then
target_wb.Save
End If
End If

End If

target_wb.Close

End Sub

 

[Georgiboy]

Another option would be to not copy the 9 rows of data to be pasted.

The row delete line could look as below:

Rows("1:9").Delete

 

[WilliamA]

Thanks for the help. The code works but with the line in its current place, it keeps deleting nine rows of data - maybe it's still looping through? I tried moving it to before the 'get quantity to create loop' step but it deletes the original data. Is there a workaround?

 

[Jimmypop]

Thanks for the help. The code works but with the line in its current place, it keeps deleting nine rows of data - maybe it's still looping through? I tried moving it to before the 'get quantity to create loop' step but it deletes the original data. Is there a workaround?

Test on sample data...

Try moving

Rows("1:9").Delete

to here...

If Not target_wb.Saved Then
If MsgBox("Do you want to save the file?", vbYesNo, "Save?") = vbYes Then
target_wb.Save
End If
End If

so it should look like:

If Not target_wb.Saved Then
If MsgBox("Do you want to save the file?", vbYesNo, "Save?") = vbYes Then
Rows("1:9").Delete
target_wb.Save
End If
End If

 

[WilliamA]

Thanks, I appreciate your help. It works in the new place, but only if I follow through with the prompts to save the file. Is there a way to make sure it either"
1. Doesn't copy over the first nine rows
or 2. Deletes the nine rows regardless of saving the file

 

[WilliamA]

Test on sample data...

Try moving

Rows("1:9").Delete

to here...

If Not target_wb.Saved Then
If MsgBox("Do you want to save the file?", vbYesNo, "Save?") = vbYes Then
target_wb.Save
End If
End If

so it should look like:

If Not target_wb.Saved Then
If MsgBox("Do you want to save the file?", vbYesNo, "Save?") = vbYes Then
Rows("1:9").Delete
target_wb.Save
End If
End If

Thanks, I appreciate your help. It works in the new place, but only if I follow through with the prompts to save the file. Is there a way to make sure it either"
1. Doesn't copy over the first nine rows
or 2. Deletes the nine rows regardless of saving the file