CODEWITHSUNDEEP
excelvba
MarkLee
MarkLee

Reputation: 13

Is there a way to copy a variable into another sheet?

Im trying to copy all cells that has GROUP in it and paste it into another sheet. This is my code so far. But I keep getting a 438 error. Im very new to vba so any help will be appreciated.

 Sub FindAndExecute()

Dim Sh As Worksheet
Dim Loc As Range

For Each Sh In ThisWorkbook.Worksheets
    With Sh.UsedRange
        Set Loc = .Cells.Find(What:="GROUP*")
        If Not Loc Is Nothing Then
            Do Until Loc Is Nothing
                'Copy the data
                Sheets("Sheet1").Value(Loc).Copy
                'Activate the destination worksheet
                Sheets("Sheet2").Activate
                'Select the target range
                Range("A1").Select
                'Paste in the target destination
                ActiveSheet.Paste
                Set Loc = .FindNext(Loc)
            Loop
        End If
    End With
    Set Loc = Nothing
Next

End Sub

Upvotes: 0

Views: 59

Answers (1)

Mehmet Canbulat
Mehmet Canbulat

Reputation: 91

I can't understand how you copy and paste multiple cells to a new worksheet. Do you want to keep source pattern or do you want to save it as dataset format? Anyway, my solution is copy source range to a new sheet and clear the cells that not match with your criteria. I tested at my pc.

Sub Test()

Dim ws As Worksheet
Dim wsa As Worksheet
Dim cll As Range

ThisWorkbook.Sheets.Add

Set wsa = ActiveSheet

lr = 1

For Each ws In ThisWorkbook.Worksheets
    If wsa.CodeName <> ws.CodeName Then
        ws.UsedRange.Copy wsa.Range("A" & lr)
        For Each cll In wsa.UsedRange.Cells
            If InStr(1, cll.Value, "Your Criteria String") = 0 Then
                cll.Value = ""
            End If
        Next cll
        lr = lr + ws.UsedRange.Rows.Count
    End If
Next ws

End Sub

Upvotes: 1

Related Questions