Copy data from one workbook to another.

[XrayLemi]

Hi all,
I am trying to write code to copy data from one workbook to another. What I need is to copy the cell value from column H in the active row to column E in another workbook. All the code I have found copies the entire last row. I managed to find code similar to what I need and manipulate it to copy a single cell. BUT, it seems to always copy the same cell in a particular row over and over. It doesn't copy the cell in the active row. Here is my code. What am I missing?

Dim wsCopy As Worksheet
       Dim wsDest As Worksheet
       Dim lCopyLastRow As Long
       Dim lDestLastRow As Long
  'Set variables for copy and destination sheets
       Set wsCopy = Workbooks("Log EP VBA template-6-21-20xxxxxxxx.xlsm").Worksheets("Book 1")
       Set wsDest = Workbooks("Test List.xlsm").Worksheets("Sheet1")
    
  '1. Find last used row in the copy range based on data in column E
       lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "E").End(xlUp).Row
    
  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
       lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "E").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
       wsCopy.Range("H" & lCopyLastRow).Copy _
       wsDest.Range("E" & lDestLastRow)
    
  'Optional - Select the destination sheet
       wsDest.Activate

 

[JLGWhiz]

It is not clear what you mean by 'active row in column H'. In vba, the active row could be anywhere on the sheet and unless the code specifically activates a certain row, vba does not regard the active row. Instead, it regards the designated row as defined by the code. In your case it is being told to use the cell in column H of the last row with data to copy, which may or may not be the active row. If you can explain what you are trying to copy from column H and the criteria for selecting the range to copy, then maybe one of the forum members can help with the code.

Here is an illustration of what I mean. Note the active cell address in the message box on each iteration.

Sub t()
Dim i As Long
Range("A1").Activate
    For i = 2 To 5
        Cells(i, i) = i - 1
        MsgBox ActiveCell.Address
    Next
End Sub

 

[JLGWhiz]

The code I posted should be run on a blank sheet.

 

[XrayLemi]

As you were doing this for me I realized I had an address wrong.
This
'1. Find last used row in the copy range based on data in column E
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "E").End(xlUp).Row
Should have been
'1. Find last used row in the copy range based on data in column H
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "H").End(xlUp).Row

I fixed it, now it works . Sorry I should really check my work more thoroughly before I post.

 

[JLGWhiz]

Glad you got it fixed and thanks for the feedback,
Regards, JLG