VBA send cells value to another sheets next empty cell-CodePudding

I am trying to send a ticker from one workbook to another when ever it is one I would like to track. When I send it to the other workbook I want to paste the ticker in the next open cell in column JW.

So far my code is as follows:

Sub S2WL()

Dim lst As Long
Dim myVar As String

myVar = ActiveWorkbook.Activesheet.Range(“C2”).Value
  
With Workbooks("Dash").Sheet("DASH")
     lst = .Range("JW" & Rows.Count).End(xlUp).Row   1
    .Range("JW" & lst).PasteSpecial xlPasteValues
End With

End Sub

I'm still very new to VBA and it fails when attempting to pull from my active sheet in workbook Fundamentals. I have multiple sheets that are basically copies in fundamentals. I want a general macro to send C2's value for whatever sheet I am on in Fundamentals to my watchlist in the Workbook Dash column JW. And each time to the next open cell in that column.

Thank you for any and all help!

CodePudding user response：

Copy Value From the Active Sheet

A Qick Fix

Sub S2WLFixed()

    Dim MyValue As Variant: MyValue = ThisWorkbook.ActiveSheet.Range("C2").Value
      
    With Workbooks("Dash.xlsx").Worksheets("Dash")
        Dim lst As Long: lst = .Cells(.Rows.Count, "JW").End(xlUp).Row   1
        .Cells(lst, "JW").Value = MyValue
    End With

End Sub

An Improvement

The following covers most of the issues you may encounter when using the previous code.

Sub S2WL()
    
    ' 1. Define constants.
    
    Const ProcName As String = "S2WL" ' for the message boxes
    ' s - Source (read from)
    Const sCellAddress As String = "C2"
    ' d - Destination (write to)
    Const dwbName As String = "Dash.xlsx" ' check if file extension is correct!
    Const dwsName As String = "Dash"
    Const dColumn As String = "JW"
    
    ' 2. Reference the source...
    
    ' Reference the workbook containing this code ('swb').
    Dim swb As Workbook: Set swb = ThisWorkbook
    
    ' Reference the active sheet ('ash').
    Dim ash As Object: Set ash = ActiveSheet
    
    ' Validate that the active sheet is in the source workbook.
    If Not ash.Parent Is swb Then
        MsgBox "The active sheet '" & ash.Name & "' is not located in the '" _
            & swb.Name & "' workbook.", vbCritical, ProcName
        Exit Sub
    End If
    
    ' Validate that the active sheet is a worksheet.
    If ash.Type <> xlWorksheet Then
        MsgBox "The active sheet '" & ash.Name & "' is not a worksheet.", _
            vbCritical, ProcName
        Exit Sub
    End If
        
    ' Reference the source cell ('sCell').
    Dim sCell As Range: Set sCell = ash.Range(sCellAddress)
        
    ' 3. Reference the destination...
        
    ' Validate the destination workbook ('dwb').
    Dim dwb As Workbook
    On Error Resume Next
        Set dwb = Workbooks(dwbName)
    On Error GoTo 0
    If dwb Is Nothing Then
        MsgBox "The destination workbook '" & dwbName & "' is not open.", _
            vbCritical, ProcName
        Exit Sub
    End If
    
    ' Validate the destination worksheet ('dws').
    Dim dws As Worksheet
    On Error Resume Next
        Set dws = dwb.Worksheets(dwsName)
    On Error GoTo 0
    If dws Is Nothing Then
        MsgBox "The destination worksheet '" & dwsName _
            & "' does not exist in the '" & dwbName & "' workbook.", _
            vbCritical, ProcName
        Exit Sub
    End If
    
    ' Reference the destination cell ('dCell').
    Dim dCell As Range
    Set dCell = dws.Cells(dws.Rows.Count, dColumn).End(xlUp).Offset(1)
    
    ' 4. Copy.
        
    ' Write the value from the source cell to the destination cell.
    dCell.Value = sCell.Value
    
    ' Save the destination workbook (decide on your own).
    'dwb.Save
    
    ' 5. Inform.
    
    MsgBox "Value copied.", vbInformation
    
End Sub