VBA to Copy/Paste from one sheet to another. Application-Defined error. I'm lost.

[shollenbmk]

Hi all, first post of mine to this forum.

I've been working with this bit of VBA for a few hours now, but haven't been able to figure it out. Needless to say, I'm not very good in VBA. I am trying to copy several rows from one sheet to another, making sure that the pasted content starts on the first empty row in the destination.

System: Excel 2010 on Windows 7

Source: Current workbook. (I know I should reference a specific filename, but the name of this file changes all the time. The macro exists inside the source workbook, so I am assume that running it from there will allow for the use of currentworkbook.)

Destination: "Example_of_TimeKeeper2016.xlsx"

What happens: The macro successfully copies the correct fields, as well as successfully opening the destination file. Unfortunately, it pastes the data in source formatting, instead of just pasting text. Also, it pastes it wherever it wants, rather than on the first blank row. Last, I always get an error saying "Run-time error '1004': Application-defined or object-defined error"

My intuition tells me that something is wrong with Range(c.Address).Select in the "paste" portion of the code, but I can't figure it out. Can anyone help?


Sub CopyRows()

secondWorkbook = "Example_of_TimeKeeper2016.xlsx"
lastColumn = "k"

currentWorkbook = ThisWorkbook.Name
Workbooks.Open ThisWorkbook.Path & "\" & secondWorkbook

Windows(currentWorkbook).Activate
With Worksheets(1).Columns("A:A")
Set c = .Find("", LookIn:=xlValues)
If Not c Is Nothing Then
secondAddress = Replace(c.Address, "$A$", "")
Range("A2:" & lastColumn & CStr(CInt(secondAddress))).Select
Selection.Copy
End If
End With

' -------- Activate the Second Workbook-----------
Windows(secondWorkbook).Activate
With Worksheets(1).Columns("A:K")
Set c = .Find("", LookIn:=xlValues)
If Not c Is Nothing Then
'------- Select and paste the data from First Workbook--------------
Range(c.Address).Select
ActiveSheet.Paste
End If
End With
End Sub

 

[shollenbmk]

--- I spoke too soon. I think it's officially broken, as it won't paste anything now. Before, it pasted something, at least. Now, it pastes nothing at all.

 

[JackDanIce]

Welcome to the board. Try:

Sub CopyRows_v1()
    
    Dim wkb     As Workbook
    Dim arr()   As Variant
    Dim rng     As Range
    Dim x       As Long
    
    Const file2 As String = "Example_of_TimeKeeper2016.xlsx"
        
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Sheets(1)
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set rng = .Cells(1, 1).Resize(x).Find("", LookIn:=xlValues)
        If Not rng Is Nothing Then arr = rng.Resize(, 11).Value
    End With
    
    Workbooks.Open ThisWorkbook.Path & "\" & file2
    
    With ActiveWorkbook.Sheets(1)
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng = .Cells(1, 1).Resize(x).Find("", LookIn:=xlValues)
        
        If Not rng Is Nothing Then rng.Resize(, UBound(arr, 2)).Value = arr
    End With
    
    Erase arr


End Sub

 

[My Aswer Is This]

Glad to see you here at Mr. Excel

I would prefer you tell me in words what you want to do and be specific.
I believe you want to copy certain rows of data from one worksheet to another worksheet in the same workbook.

I need:
1. The name of the two worksheets
2. The criteria on which rows are copied to the first empty row on the receiving worksheet.
3. The criteria should say something like this:
4. If the cell in column (G) has the value "Good" then copy this row to sheet ??
5. Do both work sheets already have headers

Please answer all these questions and I'm sure then I can help you

 

[shollenbmk]

Thank you for the quick reply and the welcome!

Unfortunately, this doesn't seem to work.

I pasted it in and ran it, and was hopeful to see that there were no error messages. Unfortunately, it doesn't seem to copy anything. I tried manually pasting and found that nothing was copied.

Does there need to be a selection.copy in there somewhere?

 

[JackDanIce]

You rarely need to use .Select or .Activate in code, it's residual from learning to code via recorded macros, hence not using Selection.Copy because nothing is selected.

The code below uses an array to store the values and then print those values back out, it's faster than using Copy+Paste.

You can try something different, what if you put a very unique value in a cell (say "qqq") and then in the macro try running as:

Sub CopyRows_v1()
    
    Dim wkb     As Workbook
    Dim arr()   As Variant
    Dim rng     As Range
    Dim x       As Long
    
    Const file2 As String = "Example_of_TimeKeeper2016.xlsx"
    
    Const srchTerm as String = "qqq"
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Sheets(1)
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set rng = .Cells(1, 1).Resize(x).Find(srchTerm, LookIn:=xlValues)
        If Not rng Is Nothing Then arr = rng.Resize(, 11).Value
    End With
    
    Workbooks.Open ThisWorkbook.Path & "\" & file2
    
    With ActiveWorkbook.Sheets(1)
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng = .Cells(1, 1).Resize(x).Find(srchTerm, LookIn:=xlValues)
        
        If Not rng Is Nothing Then rng.Resize(, UBound(arr, 2)).Value = arr
    End With
    
    Erase arr


End Sub

If that works then should be straight forward to adapt to what you need.

 

[shollenbmk]

1. The source should be similar to "Joe Pioneer 01-18 to 01-24-16.xlsx" The problem is that these dates change with every new timecard submitted.
1b. The destination will always be "Example_of_TimeKeeper2016" The end goal is to dump every employee's time card into the master excel sheet, here.

2. On the source (employee timecard), the second tab is called "tracker" and contains hours and basic info on jobs done. These fields go from A3, to K12.

3. Criteria: The area for employees to type in their hours and job notes is on the tracker tab inside the source file. This area covers the area of A3 to K12, but not all of it need be filled out.

4. The copy function should pick up only rows that have been filled out in this time tracker. Ex. An employee may do 5 jobs, which cover rows 3, 4, 5, 6, and 7. Row A is Employee name, Row B is Date, Row C is Customer, all the way to Row K, which is hours-worked on that particular job (row).

5. Both sheets have header rows. The source has Rows 1 and 2 as headers, while the destination has the same exact headers.


Thank you so much for the help! Let me know what other information I can provide. I'll be out for the late afternoon but will check back on this post later tonight.

I appreciate it!

 

[shollenbmk]

Thank you again. I'll try this a little later tonight. I have to run to a late meeting atm.

You rarely need to use .Select or .Activate in code, it's residual from learning to code via recorded macros.

You can try something different, what if you put a very unique value in a cell (say "qqq") and then in the macro try running as:

Sub CopyRows_v1()
    
    Dim wkb     As Workbook
    Dim arr()   As Variant
    Dim rng     As Range
    Dim x       As Long
    
    Const file2 As String = "Example_of_TimeKeeper2016.xlsx"
    
    Const srchTerm as String = "qqq"
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Sheets(1)
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set rng = .Cells(1, 1).Resize(x).Find(srchTerm, LookIn:=xlValues)
        If Not rng Is Nothing Then arr = rng.Resize(, 11).Value
    End With
    
    Workbooks.Open ThisWorkbook.Path & "\" & file2
    
    With ActiveWorkbook.Sheets(1)
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng = .Cells(1, 1).Resize(x).Find(srchTerm, LookIn:=xlValues)
        
        If Not rng Is Nothing Then rng.Resize(, UBound(arr, 2)).Value = arr
    End With
    
    Erase arr


End Sub

If that works then should be straight forward to adapt to what you need.

 

[JackDanIce]

You're welcome, hope it works or you can adapt to work.