Exporting certain data to new worksheet based on value in Column H

[bdouglas1011]

I have a worksheet I have built with multiple columns (A:AZ) and in Column "h" it either has a '1' or a '0'
1= On Bottom
0= Off Bottom

I would like to run a macro that would search through column "h" and if it has a "1" value in the cell to export that entire row (A thru AZ) of data into new worksheet to filter out On bottom data.

I would like that sheet called BOTTOM within the same workbook.

It seams pretty easy but i keeps coming out wrong

[HaHoBe]

Hi, bdouglas1011,

when applying the Autofilter you could do the process without using macros.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Ciao,
Holger

[bdouglas1011]

I changed Sheet 1 to LOG since the data was on that sheet and the Field I changed to 4 instead of 8 Because the column of data ended up in column 'D' not 'H' but when I run it it adds a new Sheet called bottom and then it has the header line but no data set form the filter looking for '1' in column D

[HaHoBe]

Hi, bdouglas1011,

please check as I assumed to be one row for the header (and that being the first one), and what you describe could happen if the heasder is placed in a diffferent row than 1.

Codeline

Please Login or Register  to view this content.

is meant to check if any data is to be located after row 1 so the 1 needs to be adjusted to the row of the header according to your setup.

If you still have problems please attach a sample workbook to have a look at.

Ciao,
Holger

[bdouglas1011]

I have attached a portion of the worksheet I downsized it to only have 1000 rows but it usually has 10,000 or more and sometimes much less - The data is on "LOG" worksheet. Column "d" has the "1" that we are looking for.

I also included in the workbook a Tab called Sample that is how it should look when done you can see Column "d" only returned the ones that a value of "1"

[HaHoBe]

Hi, bdouglas1011,

when working with lists or tables any complete empty row or column will end the area which you want to work with. I deleted the emtpty row 2 on Sheet LOG and changed the code to read

Please Login or Register  to view this content.

Ciao,
Holger

[bdouglas1011]

I have no idea what I'm doing wrong now when i run it nothing happens. I deleted Row 2 as you suggested. Sorry for the problems

Sorry I got it Thanks I added

Please Login or Register  to view this content.

and it worked.

I may have another question pertaining a formula if you don't mind in bit.

[bdouglas1011]

I noticed that when it completes the Sheet "Bottom" is Selected can you tell it to deselect the page in the code.

I also use a form of this formula all the time but I really never know my entire range is there a better way to rewrite this VBA VLOOKUP to cover the entire range any given set of data I am using ....it changes every time

I would hate to have to alter 20 of these formulas every time.

Please Login or Register  to view this content.

[HaHoBe]

Hi, bdouglas1011,

maybe give a name to the range you want to search in or maybe refer to the entire columns. Both ways would prevent rewriting or adapting the formulas if the range changes.

Ciao,
Holger