copying entire row from one sheet to another

[EddyAddelan]

hey all....

i've managed to find this super codes what someone wrote along time ago(sometime in 2005)...a whole part of it helped me in whatever i'm trying to achieve...but there's still some minor problems that i've encountered..

this is the original code

Please Login or Register  to view this content.

this code helps you to search a word/phrase in sheet1 and copy the whole row, then pasting it onto a new sheet....

my problem is, in my case i have several sheets to combine with....whereas this one only identify 1 particular sheet (see bolded section in the code)
can anyone out there help me with this ?

appreciate your help...
cheers

[Carim]

Hi,

What about multifind ...from Ron

Please Login or Register  to view this content.

HTH
Carim

[EddyAddelan]

Hi,

What about multifind ...from Ron

Please Login or Register  to view this content.

HTH
Carim

hye carim...
not to sound like such a noob(which i'm unfortunately am )...where should i put that in ?

i do have a command button code that i need to fill in
eg.

Please Login or Register  to view this content.

mfg..
eddy

[davesexcel]

Just to add to Carims macro

Please Login or Register  to view this content.

[EddyAddelan]

dear davesexcel...

sorry but that didn't bring much help at all...
i'm not sure whether or not you guys understood what i actually wanted from the original code above....

[davesexcel]

Sorry about that, I was just looking at carims code to open the find dialog box and thought what if I added this or that to it,

You need to loop throught the worksheets to find your title instead of just one sheet, I'll work on it

[EddyAddelan]

Sorry about that, I was just looking at carims code to open the find dialog box and thought what if I added this or that to it,

You need to loop throught the worksheets to find your title instead of just one sheet, I'll work on it

thanks for the help....
looking forward to your solution...

mfg..
eddy

[T-J]

my problem is, in my case i have several sheets to combine with....whereas this one only identify 1 particular sheet (see bolded section in the code)
can anyone out there help me with this ?

appreciate your help...
cheers

The code may be extended to search all sheets (except the results sheet) by looping through the Worksheets Collection:

For Each ws in Workbook.Worksheets
search, copy and paste
Next ws

Please Login or Register  to view this content.

[jazbath]

Hi,
The following code was written to search 12 sheets and put results in a sheet called "Summary Sheet"

Sub test2()
Dim SHTNM As String
Dim R As Long
Dim Z As String
Application.ScreenUpdating = False

R = 2 ' Summary Row to start paste of InProd rows from other sheets
Sheets("Source").Range("A1").Value = InputBox("What do you like to search")

Z = Sheets("Source").Range("A1").Value


For sht = 1 To 12 ' sheets ( 1 to 12) production sheets
SHTNM = "ProdLine" & sht
Sheets(SHTNM).Select
With Sheets(SHTNM).Range("A:A", Range("A100").End(xlUp).Address)
Set c = .Find(Z, LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
Do
Sheets(SHTNM).Range(c.Address).EntireRow.Copy
Sheets("Summary").Cells(R, 1).Insert Shift:=xlDown
R = R + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


Next sht
Sheets("Summary").Select
End Sub


this might help you.
Jaz

[EddyAddelan]

dear jaz and T-J...
thanks alot for ur help, the guides that you guys gave really helped me alot..
another thing though, both of you assigned for the search function to search for ALL the sheets EXCEPT the summary/result sheet...the thing is, in my workbook, there so many other sheets that i don't want to be included...
can't i just assign the names of the sheets that needed to be included ? if so, how am i to do it ?

cheers
eddy

[T-J]

If using the Worksheets Collection, use a Select Case to check the worksheet name:

Please Login or Register  to view this content.

[EddyAddelan]

If using the Worksheets Collection, use a Select Case to check the worksheet name:

Please Login or Register  to view this content.

hey T-J,
the code that u gave me works fine, but now a new problem has raised...
the problem by your code is that, it pastes the info from sheet1, but then it pastes the new info from sheet2 OVER infos from sheet1...

tried manupilating it myself, but the inputbox keeps coming out, and i have to enter what i wanted to find twice...

here's the list of my actual VBA codes that i have...

Please Login or Register  to view this content.

because i don't actually know how to do it, i HAD to re-write the codes again(the search-copy-paste function)i know there's an easier way....just can't get to it

cheers
eddy

[T-J]

hey T-J,
the code that u gave me works fine, but now a new problem has raised...
the problem by your code is that, it pastes the info from sheet1, but then it pastes the new info from sheet2 OVER infos from sheet1...

That's because it always pastes starting at row 2 (IntS = 2).
You need to get the last used row on the results sheet and paste to the row after:

Please Login or Register  to view this content.

Note the use of a Long Integer type instead of Integer (max 32,767) to prevent overflow.

[EddyAddelan]

That's because it always pastes starting at row 2 (IntS = 2).
You need to get the last used row on the results sheet and paste to the row after:

Please Login or Register  to view this content.

Note the use of a Long Integer type instead of Integer (max 32,767) to prevent overflow.

where should i insert those codes exactly ?

[T-J]

where should i insert those codes exactly ?

At the top, after the variable declarations:

Please Login or Register  to view this content.

In the copy/paste Do..Loop (replace what's there):

Please Login or Register  to view this content.