Copying rows to new sheet with search function

Srelie

New Member
Joined
Dec 15, 2016
Messages
12
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.

Code:
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Rich (BB code):
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

New Member
Joined
Dec 15, 2016
Messages
12
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 
Last edited:

Srelie

New Member
Joined
Dec 15, 2016
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 15, 2016
Messages
12
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 15, 2016
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top