try to Copy entire Row if active cell equals variable

Hey all,

I've been working on this for about a week and am having some troubles with the final steps. This is my very first attempt at VBA with ZERO experience with it, so please be kind if this is an extremely Noobie question.

I cannot for the life of me get the macro to copy the entire row. I know it has to be a something I'm doing wrong in the code. The closest I have come is getting the macro to copy a single cell. Below is the macro code as it sits right now. Any help would be very much appreciated.

Sub TestingPayrolldate()
    
    'Declared Variables
    Dim payrolldate As Range
    Dim copyrange As Range
    
    Application.ScreenUpdating = False
    
    Range("F1").Select
        
    payrolldate = Application.InputBox(Prompt:="Please enter the Payroll Starting Date", Default:="YYYYMMDD")
    
    Do
        ActiveCell.Offset(1, 0).Select
        
        If ActiveCell.Value = payrolldate Then
      
        Range.Offset(0, -5)activecell.end (xltoright).Copy
                
        Workbooks.Add
        
        Selection.Paste
        
        ActiveCell.Offset(1, 0).Select
        
        Worksheets(Labor_Transfer_Table).Activate
        
    
    
    ElseIf payrolldate = "" Then MsgBox "No Payroll records with that Starting Date Exists"
    
    End If
          
    Loop Until ActiveCell.Value = ""
    
    MsgBox "Search Completed"
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    ActiveWorkbook.SaveAs "C:\Users\matt_f\Desktop\PaycorOnsite\Time_Clock_Import\Pre-conversion\Payroll", xlCSV
    ActiveWorkbook.Save
    
        
    MsgBox " has been created and saved"
    
End Sub

Hi Mfederman

Welcome to the Forum.
In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

Would be ideal to see a sample workbook. Start with changing this line of code

Range.Offset(0, -5)activecell.end (xltoright).Copy

to

Range.Offset(0, -5).EntireRow.Copy

Are you wanting to copy all the rows that match the payroll input date to a new workbook.
Your variable

Dim payrolldate As Range

is giving the error

Please rather add a sample file...not a image. Cannot do tests on a image.
Originally Posted by Pete_UK
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Change

payrolldate = Application.InputBox(Prompt:="Please enter the Payroll Starting Date", Default:="YYYYMMDD")

to

payrolldate = InputBox("Please enter the Payroll Starting Date", Default:="YYYYMMDD")

Please rather add a sample file...not a image. Cannot do tests on a image.

Try this: Not happy with the loop. Perhaps someone can add code with a Do While syntax

Option Explicit
Private Sub CommandButton1_Click()
Dim payrolldate As Long
Dim nWb As Workbook, wb As Workbook
Dim lRow As Long, nRow As Long
Dim i As Integer
Dim copyrange As Range
Dim ws As Worksheet, ns As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Labor_Transfer_Table")
Set nWb = Workbooks.Add
Set ns = nWb.Sheets("Sheet1")
lRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
payrolldate = InputBox("Please enter the Payroll Starting Date", Default:="YYYYMMDD")
ws.Activate
With ws
    For i = 2 To lRow
        If Cells(i, 6) = payrolldate Then
            nRow = ns.Range("A" & Rows.Count).End(xlUp).Row + 1
            Cells(i, 6).EntireRow.Copy Destination:=ns.Range("A" & nRow)
            ws.Activate
        Else
            MsgBox "Search Completed,No Payroll records with that Starting Date Exists"
            GoTo SaveMe
        End If
    Next i
End With
SaveMe:
ns.Activate
With ns
    .SaveAs Filename:="C:\Users\matt_f\Desktop\PaycorOnsite\Time_Clock_Import\Pre-conversion\Payroll.csv", FileFormat:=xlCSV
    ActiveWorkbook.Saved = True
End With
MsgBox "Has been created and saved"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Quit
End Sub

Originally Posted by Mfederman

You are awesome. I REALLY appreciate you taking the time to put this together!
I just copied and pasted the code in to a new module and tried to step through and got a new error:

"Run-time error '6': Overflow"

I'm not sure what that means.

The line that gets highlighted when I hit debug is

for i = 2 To lRow

"

Yep, sorry...Need to declare i As a Double and not Integer
So

Dim I As Integer

must be

Dim i As Double

EDIT: But seriously go with my second code...Post # 13 Works much better.

Go with this code...Do while loop

Option Explicit
Private Sub CommandButton1_Click()
Dim payrolldate As Long
Dim nWb As Workbook, wb As Workbook
Dim lRow As Long, nRow As Long
Dim i As Double
Dim copyrange As Range
Dim ws As Worksheet, ns As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Labor_Transfer_Table")
Set nWb = Workbooks.Add
Set ns = nWb.Sheets("Sheet1")
lRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
ErrorHandler:
payrolldate = InputBox("Please enter the Payroll Starting Date", Default:="YYYYMMDD")
ws.Activate
With ws
i = 2
    If Cells(i, 6) = payrolldate Then
        Do While Cells(i, 6).Value = payrolldate
            nRow = ns.Range("A" & Rows.Count).End(xlUp).Row + 1
            Cells(i, 6).EntireRow.Copy Destination:=ns.Range("A" & nRow)
            ws.Activate
            i = i + 1
        Loop
    Else
        MsgBox "No Payroll records with that Starting Date Exists"
        GoTo ErrorHandler
    End If
End With

ns.Activate
With ns
    .SaveAs Filename:="C:\Users\matt_f\Desktop\PaycorOnsite\Time_Clock_Import\Pre-conversion\Payroll.csv", FileFormat:=xlCSV
    ActiveWorkbook.Saved = True
End With
MsgBox "Has been created and saved"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Quit
End Sub

Oops sorry....My destination folderpath is in code
Herewith amended....See both files and amended code in Post13

Search for a date that is in the first couple of rows and

Dim i As Long

Is it possibly something to do with our different versions of excel? I see you're in 2007 and I'm using 2010.

Don't think so...

And your dates are in that format YYYYMMDD
EDIT:
Change

Dim payrolldate As Long

to

Dim payrolldate As String

Have you tested my attached and seen it works.

Search for a date that is in the first couple of rows and Dim i As Long

Not sure what else to do....Create a new sample with a few of your current entries and test the code again....Also upload so that we can test. See if there is a difference.
Edit:

Herewith code that solves this Threads issue.

Option Explicit
Private Sub CommandButton1_Click()
Dim payrolldate As String
Dim nWb As Workbook, wb As Workbook
Dim lRow As Long, nRow As Long
Dim i As Double
Dim copyrange As Range
Dim ws As Worksheet, ns As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Labor_Transfer_Table")
Set nWb = Workbooks.Add
Set ns = nWb.Sheets("Sheet1")
lRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
ErrorHandler:
payrolldate = InputBox("Please enter the Payroll Starting Date", Default:="YYYYMMDD")
ws.Activate
With ws
For i = 2 To lRow
    If Cells(i, 6) = payrolldate Then
        Do While Cells(i, 6).Value = payrolldate
            nRow = ns.Range("A" & Rows.Count).End(xlUp).Row + 1
            Cells(i, 6).EntireRow.Copy Destination:=ns.Range("A" & nRow)
            ws.Activate
            i = i + 1
        Loop
        ns.Activate
            With ns
                .SaveAs Filename:="C:\Users\matt_f\Desktop\PaycorOnsite\Time_Clock_Import\Pre-conversion\Payroll.csv", FileFormat:=xlCSV
                ActiveWorkbook.Close
                ActiveWorkbook.Saved = True
            End With
            MsgBox "File has been created and saved"
            GoTo EndMe
        ElseIf i = lRow Then
            MsgBox "No Payroll records with that Starting Date Exists"
            GoTo ErrorHandler
    End If
Next i
End With
EndMe:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Quit
End Sub