if blank go to next
 

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 pages is all pasted
to a tab at the end of the workbood.

Thanks in advance.


--
duhrl
------------------------------------------------------------------------
duhrl's Profile: http://www.thecodecage.com/forumz/member.php?userid=126
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62341

if blank go to next
 

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







"duhrl" wrote:


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 pages is all pasted
to a tab at the end of the workbood.

Thanks in advance.


--
duhrl
------------------------------------------------------------------------
duhrl's Profile: http://www.thecodecage.com/forumz/member.php?userid=126
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62341

if blank go to next
 

duhrl;224895 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62341

if blank go to next
 

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

thanks for your help.


--
duhrl
------------------------------------------------------------------------
duhrl's Profile: http://www.thecodecage.com/forumz/member.php?userid=126
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62341

if blank go to next
 

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

thanks for your help.Durhl, we still need a sample workbook or the code that you are using
for your copy in order to customise it to your needs.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62341

if blank go to next
 

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.

"duhrl" wrote:


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

thanks for your help.


--
duhrl
------------------------------------------------------------------------
duhrl's Profile: http://www.thecodecage.com/forumz/member.php?userid=126
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62341