Copying rows to new sheet with search function

[Srelie]

I am new to VBA and am in need of some help.

My goal is to copy rows to a new sheet, based on a search from a set list.

I found a macro via a google search earlier and tried to modify it to my needs, but I'm not competent enough yet.

Sub Search()
  'If value in column B = Matches anything in column G, copy entire row to Sheet2
  Dim searchTerm As String
  For I = 1 To 1130
      searchTerm = ActiveSheet.Range("G" & I).Text
      If ActiveSheet.Range("B" & CStr(LSearchColumn)).Value = searchTerm Then
         'Select row in Sheet1 to copy
         Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
         Selection.Copy
         'Paste row into Sheet2 in next row
         Sheets("Sheet2").Select
         Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
         ActiveSheet.Paste
         'Move counter to next row
         LCopyToRow = LCopyToRow + 1
         'Go back to Sheet1 to continue searching
         Sheets("Sheet1").Select
      End If
Next I
End Sub

I'm getting this error: Run-time error '1004'


Thank you.

 

[JoeMo]

I am new to VBA and am in need of some help.

My goal is to copy rows to a new sheet, based on a search from a set list.

I found a macro via a google search earlier and tried to modify it to my needs, but I'm not competent enough yet.

Sub Search()
  'If value in column B = Matches anything in column G, copy entire row to Sheet2
  Dim searchTerm As String
  For I = 1 To 1130
      searchTerm = ActiveSheet.Range("G" & I).Text
      If ActiveSheet.Range("B" & CStr(LSearchColumn)).Value = searchTerm Then
         'Select row in Sheet1 to copy
         Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
         Selection.Copy
         'Paste row into Sheet2 in next row
         Sheets("Sheet2").Select
         Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
         ActiveSheet.Paste
         'Move counter to next row
         LCopyToRow = LCopyToRow + 1
         'Go back to Sheet1 to continue searching
         Sheets("Sheet1").Select
      End If
Next I
End Sub

I'm getting this error: Run-time error '1004'


Thank you.

You haven't declared or initialized the variables in red.

 

[Srelie]

Alright... still having difficulty.

I declared the variable and still got the error. Perhaps, I did not declare and initialize them correctly.

Could you give me a proper example?

 

[JoeMo]

Alright... still having difficulty.

I declared the variable and still got the error. Perhaps, I did not declare and initialize them correctly.

Could you give me a proper example?

Assuming those variables are lnteger values:

Dim LSearchColumn as Long

will declare the variable, but unless and until you initialize it, the variable will have the default value of zero (0). There is no cell with an address of B0 so this line:

ActiveSheet.Range("B" & CStr(LSearchColumn)).Value

will cause a run time error. You might have an easier path to your goal, if you tell us what you want your macro to accomplish and provide enough detail that someone not familiar with your task can help you formulate some code.

 

[Srelie]

That seems to be the problem. Thank you for explaining.


So, what I need this macro to be able to search a column for a value from a list, then copy and paste the entire row(that contains the value searched for) into another sheet. The catch is that there are several rows with any given value, and that the list of values to be searched needs to loop. Also, on the second sheet the pasted values need to start at the next empty row.

Hopefully, I am explaining this thoroughly...

 

[JoeMo]

That seems to be the problem. Thank you for explaining.


So, what I need this macro to be able to search a column for a value from a list, then copy and paste the entire row(that contains the value searched for) into another sheet. The catch is that there are several rows with any given value, and that the list of values to be searched needs to loop. Also, on the second sheet the pasted values need to start at the next empty row.

Hopefully, I am explaining this thoroughly...

Not quite. Your explanation lacks the specific details that one needs in order to write a routine that will do what you want. What column holds the list? Where does the list begin in that column? What is the name of the "other" sheet that gets the paste, and in what column on that sheet should the paste begin?

 

[My Aswer Is This]

Srelie

There is nothing here with specifics:
We need specifics.

You said:
So, what I need this macro to be able to search a column for a value from a list, then copy and paste the entire row(that contains the value searched for) into another sheet.

 

[Srelie]

Not quite. Your explanation lacks the specific details that one needs in order to write a routine that will do what you want. What column holds the list? Where does the list begin in that column? What is the name of the "other" sheet that gets the paste, and in what column on that sheet should the paste begin?

The column that holds the list is column G and it begins in row 1.

The column that holds the values is column B and begins at row 1.

The sheet that gets the paste is named sheetPaste. The paste can begin at A1.


There are approximately 26000 values to search through and 1200 items in the list.

 

[JoeMo]

You say you want to copy the entire row if a member of the list from col G is found in col B. Let's say the value in G1 is found in B7, do you want to copy the entire row 7 (which will include part of the list -> in G7) to "sheetPaste"? Please list a small sample of items in the list in col G and cell contents for cells in col B that you would want to declare a match to the col G cells. This will help us determine whether you are looking for partial matches or entire matches.

 

[Srelie]

Yes! For example:
B7 = 100-050115-01

G1= 050115-

Columns A and C sometimes also contain the value in Column G. The entire row of information is needed, except column G. It only exists as a list for searching. Only columns A-D contain information otherwise and I need that copied to the new sheet.


Please excuse my formatting, I am typing on my mobile phone.