Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria) | Excel Discussion (Misc queries) | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Macro code to test for blank row and insert blank row if false | Excel Programming |