Macro Beginner needs help please

[srfeldman]

Hi,

Made a big macro to collect cell data based on finding a text string, moving cell position down 3, across 1, and then selecting and copying all the rows with data in that column to another worksheet. Worked great until the next month when the new original data had the text strings in different columns. It still finds the text string, but pulls the wrong range of data to copy.

I've been seeing posts about variable ranges using dim, but I don't understand how to code it.

Here's and example of what I want to do.

In sheet named "Original"
Find the text string "REG"
Move down 3 and right 1
Select/copy the next 150 rows in the column
Paste that selection to Column G, row 5 in sheet named "Select"
Add column header text "REG" to Column G, row 3

If I can get that to work, I can copy the code for the other 18 text strings.

Any help is greatly appreciated. Thanks.

 

[JoeMo]

Will the string "REG" appear more than once in the sheet "Original"?

 

[srfeldman]

Will the string "REG" appear more than once in the sheet "Original"?

No. Just once for each string of text.

 

[JoeMo]

No. Just once for each string of text.

This is aimed at the specific string "REG". You can handle other strings by changing the variable sWhat to an array - e.g. sWhat = Array("String1", "String2", ...), but you must also add a line to find the next empty row in colg G of the destination sheet unless you want to overwrite previous pastes to that column.

Sub srfeldman()
Dim R As Range, sWhat As String
sWhat = "REG"
With Sheets("Original")
    Set R = .Cells.Find(what:=sWhat, LookIn:=xlFormulas, lookat:=xlPart)
    If Not R Is Nothing Then
        Application.ScreenUpdating = False
        R.Offset(3, 1).Resize(150).Copy Destination:=Sheets("Select").Range("G5")
        Application.CutCopyMode = False
        Sheets("Select").Range("G3").Value = "REG"
        Application.ScreenUpdating = True
    Else
        MsgBox sWhat & " not found on sheet Original"
    End If
End With
End Sub

 

[srfeldman]

Wow! That is awesome. Thanks so much Joe. I wish I understood it better. Any macro classes you recommend?

Best,
Scott

 

[JoeMo]

Wow! That is awesome. Thanks so much Joe. I wish I understood it better. Any macro classes you recommend?

Best,
Scott

You are welcome - thanks for the reply.

There's lots of material on the web for self education. Hiker95 has often posted an extensive list of books, websites, etc that is helpful for learning VBA. You might search Hiker95's threads to look for the list.