if blank go to next

[duhrl]

I have a workbook, about 20 tabs, that once all data has been placed i
them i need to run a macro that pulls from a search criteria. My proble
is with my limited knowledge of macro's and almost nothing of VBA I nee
the macro to do nothing if the current tab has none of the items for th
current search. the data from each of the 20 or so pages is all paste
to a tab at the end of the workbood.

Thanks in advance

 

[JLGWhiz]

There is a lot that you did not specify, Is the
search data to be found in a single column or just
anywhere in the sheet? What do you want to extract
from the sheet and put in the last sheet? If the
cells have formulas, do you want the formulas to
be carried over to the last sheet? The sample code
below assumes a single occurrence of the search
data per sheet, and that you would want to copy
the row that the search data is found on to the
last sheet. The rows will be put in the last
sheet in the order that they are found.

Sub version()
Dim sNdx As Long, lstSh As Worksheet,lrAs Long
Dim f As Range
sNdx = ThisWorkbook.Sheets.Count
Set lstSh = Sheets(sNdx)
SrchDat = 'Whatever you want to search
For Each sh In ThisWorkbook.Sheets
Set f = Sh.Find(SrchDat, LookIn:=xlValues)
If Not f Is Nothing Then
lr = lstSh.Cells(Rows.Count, 1).End(xlUP).Row
Sh.f.EntireRow.Copy lstSh.Range("A" & lr + 1)
End If
Next
End Sub

 

[Simon Lloyd]

I have a workbook, about 20 tabs, that once all data has been placed in
them i need to run a macro that pulls from a search criteria. My problem
is with my limited knowledge of macro's and almost nothing of VBA I need
the macro to do nothing if the current tab has none of the items for the
current search. the data from each of the 20 or so Tabs is all pasted to
a tab at the end of the workbook.

Thanks in advance.durhl, welcome to The Code Cage, why not attach a sample workbook so we

can see your structure and help you directly with that.

*Attatchments.*

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attatchments button,
this is where you get to add files for upload, if you have any trouble
please use this link or the one at the bottom of the
any page.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[duhrl]

My appologies, what I have is data from customer accounts and I a
running 3 different filters, Collateral, Fails, and Other. there are n
formula's in any cells. my problem is with my basic knowledge of macro
when I run into a day that there is no data it wants to copy all th
blank data and paste it on the tab i want all that data in. that cause
a problem when the blank data is larger then space available so th
macro stops and I get an error telling me not enough space. So I nee
something that will say if no data to copy then move to the next step i
the macro and continue accordingly.

thanks for your help

 

[Simon Lloyd]

My appologies, what I have is data from customer accounts and I a
running 3 different filters, Collateral, Fails, and Other. there are n
formula's in any cells. my problem is with my basic knowledge of macro
when I run into a day that there is no data it wants to copy all th
blank data and paste it on the tab i want all that data in. that cause
a problem when the blank data is larger then space available so th
macro stops and I get an error telling me not enough space. So I nee
something that will say if no data to copy then move to the next step i
the macro and continue accordingly.

thanks for your help.Durhl, we still need a sample workbook or the code that you are usin

for your copy in order to customise it to your needs

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[JLGWhiz]

To get more meaningful results for your postings, be sure to include the
relevant code that is not producing the desired results. That way, those who
respond to your posting will not have to guess at what you are attempting to
do.