0

I'm trying to create a macro that takes data from a set and pastes it into the corresponding spreadsheet based on the index name matching the spreadsheet name.

I need to paste the data below a specific string and keep adding on line by line until it hits a new spreadsheet and then it'll search for that string and do the same thing.

I'm running into an "object or with block variable not set" error on the following line

If c <> c.Offset(-1, 0) Then
           row = row

But I've set row as a range, so I'm not sure why this is happening. Please have a look at the code below.

Thank you

Sub sf()

Dim ws As Worksheet, sh As Worksheet
Dim rng As Range, c As Range
Dim row As Range
Dim rng2 As Range

Set ws = Sheets("Sheet1")
Set rng = ws.Range("A2:A10")

Set rng2 = ActiveSheet.Columns("A:A")
Set row = rng2.Find(What:="String", LookIn:=xlValues)

For Each sh In Sheets
    For Each c In rng.Cells
        If c <> c.Offset(-1, 0) Then
           row = row
        Else
           row = row.Offset(2, 0)
        End If
        If sh.Name = c Then
            ws.Range("A" & c.row & ":J" & c.row).Copy
            sh.Range("A" & row).PasteSpecial xlPasteValues
        End If
    Next c
Next sh


End Sub
mlombar9
  • 11
  • 2
  • 1
    That means `row` is `Nothing`, i.e. the `rng2.Find(What:="String", LookIn:=xlValues)` did not actually find "String". When using `Find`. always specify `What`, `LookIn`, and `LookAt`, and then test if the `Find` succeeded as demonstrated in the linked thread. – BigBen Apr 06 '22 at 16:08

0 Answers0