0
votes

I am working on a macro that loops over a varying range (O2 till the last used cell in row O in the last sheet) and copies every value into a sheet called "Overview" offset by 3 rows down and in the column behind the last used column in "Overview" if that value is not 0. Afterwards, if there is any value in the range that is not 0, I want to copy o1 from the last sheet to the first cell in the column behind the last used column in the sheet "Overview" (that will include the header). I built some code based on other snipets of code that I am already using in other macros that are working and changed the rows and columns accordingly. However, now I get a object variable or with block variable not set error. I can't find the error, can somebody help me and tell me what I need to change (and maybe also how?). Here is what I already have:

Sub Sendmemoremetrics()
    '
    ' Sendmemoremetrics Macro
    '
    Dim cRange As Range
    Dim valuessendmemore As Range
 
    Dim Cell As Range
    Dim wsDestination As Worksheet, wsSource As Worksheet
        
    'set worksheets
    With ThisWorkbook
        Set wsSource = .Worksheets(Sheets.Count)
        Set wsDestination = .Worksheets("Overview")
    End With

    LastRow1 = Sheets(Sheets.Count).Cells(Rows.Count, "O").End(xlUp).Row
    LastColumn1 = wsDestination.Cells(5, wsDestination.Columns.Count).End(xlToLeft).Column
    cRange = wsSource.Range(wsSource.Cells(2, 15), wsSource.Cells(LastRow1, 15))

    For Each Cell In cRange
        If Cell.Value > 0 Then
            wsDestination.Cells(Cell.Row, LastColumn1).offset(3, 1) = Cell.Value
            Sheets(Sheets.Count).Range("O1").Select
            Selection.Copy
            Sheets("Overview").Range(4, LastColumn1).Paste
        End If
    Next Cell

    Call format_headlines
End Sub
1
In which line do you get the error?Pᴇʜ
Try Set cRange = wsSource.Range(wsSource.Cells(2, 15), wsSource.Cells(LastRow1, 15)) instead of cRange = wsSource.Range(wsSource.Cells(2, 15), wsSource.Cells(LastRow1, 15)).FaneDuru
@Pᴇʜ in the line with cRangeMeisje17
Didn't the above suggestion solve your problem?FaneDuru

1 Answers

0
votes

As @FaneDuru already answered, try with:

Set cRange = wsSource.Range(wsSource.Cells(2, 15), wsSource.Cells(LastRow1, 15))

The range object should be explicitly "Set"