Previous Topic Next Topic

Search 4 worksheets, copy rows to 5th

Closed
Frank H. - Jun 7, 2016 at 11:48 AM
 Blocked Profile - Jun 7, 2016 at 04:47 PM
Hello,

I am using Excel 2010. I am new to the forum and have a very basic knowledge of VBA.

I am trying write a macro that takes info provided by the user in the form of an InputBox, searches four worksheets, copies the row if the input is found, and pastes the row in the fifth worksheet.

My code so far:

Sub customCopy()

Dim strsearch As String, lastline As Integer, tocopy As Integer
Dim ws As Excel.Worksheet
Dim iIndex As Integer

'Attempting to get the Macro to search the first four worksheets
For iIndex = 1 To ActiveWorkbook.Worksheets.Count
Set ws = Worksheets(iIndex)
ws.Activate

'the Range is different for each worksheet
'The input will be in columns A-C
strsearch = CStr(InputBox("Enter the Name or ELID to search for"))
lastline = Range().End(xlUp).Row
j = 1

For i = 1 To lastline
For Each c In Range("A" & i & ":C" & i)
If InStr(c.Text, strsearch) Then
tocopy = 1
End If
Next c
If tocopy = 1 Then
Rows(i).Copy Destination:=Sheets("Output").Rows(j)
j = j + 1
End If
tocopy = 0
Next i

End Sub

Any help, no matter how basic will be greatly appreciated.

Thank you.
Related:

1 response

Answer 1 / 1
Blocked Profile
Jun 7, 2016 at 04:47 PM
So, you loop through the variables, and you are only keeping track of tocopy once. You need to build an array, so you know what number is a tocopy. Like so: tocopy(c)=1

would load the tocopy counter, then loop though and get the value from the i counter.

if tocopy(i)=1 then paste_the_value_from_cell_i

Try that!

You could say tocopy=tocopy+t (or tocopy++), but that only keeps track of how many rows to copy, and not the particular row! Tocopy(c) will keep track of what rows, as each tocopy(n) can have a separate value (it is an array!) So you could in practice have
topcopy(1)=0 Actual cell (A1)
topcopy(2)=1 Actual cell (A2)
topcopy(3)=0 Actual cell (A3)
topcopy(4)=1 Actual cell (A4)

This array would get the values out in the second loop and would copy the values of: A2, and A4

Make certain you reference the value to load from cell when you load the array, dont make the code save both the array variable (1), then look for the data (a1). Load the data at the time you know you want to copy it.

Example:
If InStr(c.Text, strsearch) Then

topcopy(c)=c.text

Now loop the array, and you get your copied data!



0