VBA - Copy columns to a new spreadsheet based on date filter

[WilliamA]

Hello,

I am using VBA code from helpful information online to copy x number of columns from spreadsheet A to a new spreadsheet. The code I have provides the user with prompts to select the original spreadsheet, choose the number of columns to copy, select the respective columns, and save the final product.

I'd like to add the following
1. Delete the first 9 rows of what gets pasted (it is mostly empty space and unnecessary)
2. A date filter (what needs to get copied will differ depending on the work and will cross different ranges of dates)
3. Make the code work across tabs (the original spreadsheet where the data gets copied from has many tabs, and while all of the columns I want to copy are from the same tab, the code won't allow me to switch to another tab to copy the columns - I haven't figured out how the current code doesn't allow this)


I tried adding this line of code:

Rows(9).EntireRow.Delete

to delete the first 9 rows however it did not work.

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

I was trying something with input boxes to enter the date based on whichever column the user selects but I haven't been able to figure this out.

    'Prompt the user to input the start date
    StartDate = InputBox("Please enter the start date")
    
    'Prompt the user to input the end date
    EndDate = InputBox("Please enter the end date")

And filter the data to those within the start and end dates

        .AutoFilter Field:=lngDateCol, _
                    Criteria1:=">=" & StartDate, _
                    Criteria2:="<=" & EndDate

I hope this explains what I'm after and I appreciate the help.