Pulling Data from Filtered Rows on One Tab into another

I have a roster program for the residents in the nursing home i run. I have a master listing tab where all my data is, and it pulls into about 8 other tabs for varying info needs. The problem i am running into is one labelled snack stickers. I have pulled all the info into one tab labelled snack data, and then copied that tab into snack data filtered. I have filtered out the lines with zero or blank values for a labelled snack, and have also filtered into home area, and snack time. What i cant figure out is when i am pulling this data into my next tab "snack stickers" how do get the data to populate when the filtered data is on different rows ie row 2, row 36, row 42 when i would normally be pulling data from cell one to cell 25 and using the fill down option. I have attached the worksheet, please help me figure this out if you can, its been driving me squirelly the last week. Thank you in advance if anyone can help.

Hi

What should be on Snack Stickers? If what is there isn't right, what should it be?

rylo

The info from the rows on snack data filtered, if you look at the first sticker, it lists all the info i require.I cant figure out how to get the filtered rows to fill into the snack stickers tab without doing it manually.

Hi

It would be really messy trying to do this with formulas, so how about a macro.

I've not brought in any of the date items as I wasn't sure if this was really supposed to be a fixed date, or today's date. There is also a lot of links to cells that don't have values.

Please Login or Register  to view this content.

rylo

I tried it, and it gives me an error cannot run after break mode, and only comments after end sub. Can you send me the workbook that you ran the macro in, just so i can make sure i am seeing what you see, I'm not overly experienced in macros, so i prob messed something up, many thanks

Hi

I just ran it in the example workbook that you have put in post #1. You already have a macro in there, so I just put it after that macro.

It may pay to remove all the existing data from Snack Data - Filtered first tho incase that is causing a problem.

rylo

I had a buddy come over and look at it, and he showed me a line that's causing the problem
DataSH.Range("A:F").AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("H1:I2"), copytorange:=OutSH.Range("A1:F1")

Can you take a look at it and let me know if you can figure out what is causing the problem. I would really, really appreciate it.

Regards
Joe

Joe

Put the code into the workbook that you are testing it on and causing problems, then attach to the post. I wan't to see exactly what is happening with what you are working with.

rylo

I have inserted it, with the macro on the page i am trying to get it to work on. If you can help me with this, i will be forever grateful.

Thank you in advance

Hi

I opened the file, made a copy of sheet Snack Stickers for reference, cleared out the data on sheets Snack Data - Filtered and Snack Stickers, then ran the macro. It didn't error, and produced what to me looks like relevant data.

When you open the file you have attached, document the exact steps you do, and advise what goes wrong where / when.

rylo

I tried the macro as you said and it did pull up the info, but it doubled some info up for the same resident, and the layout i need different. I had assumed that if i cut and paste the cells where i wanted them, when i ran the macro again, they would stay the same. I will show you how i need the layout to be, so i dont know what needs to be changed in the macro. For example, on snack stickers tab, for the first resident it pulls up AM Snack twice, super pudding twice, and texture twice. I only need this information once. I will copy and paste them to how i need the cells to look for layout purposes. Thank you so much for your help thus far...we are definitely headed in the right direction. As well, i keep merging cells, and when the macro runs, it keeps unmerging them on me. The top page is the layout i need to get them to work with the labels, and the page at the bottom is how they come out, doubling up on information

Joe

I'm thoroughly confused regarding how you want your final output. I tried to replicate the output you had in your original post.

Can you please put up exactly how the output should be from the data given, and I'll review.

rylo

I have the snack stickers exactly as I need them. Sorry for the confusion, i thought i had them right last time. If you can still help, I would greatly appreciate it.

Hey Rylo,tried it and it states "unexpected end sub", and it highlights the first line of the macro in yellow? Something i did wrong? Do i need to delete the end sub and the end of the macro?

Originally Posted by rylo

HI

OK, I think I have it except for some of the formatting. I'll leave that sort of fine tuning to you.

Please Login or Register  to view this content.

rylo

Hi

my guess would be that there is an extra end sub hanging over from something else, or this macro was put into an existing sub somehow.

Try just removing the extra one and see what it does.

rylo

I tried it and running into errors. Its either telling me i cant record a macro(i've gone back in and deleted any i found), or compile error invalid outside procedure on 2nd line highlighting SH, and then at the botton Application.Run is highlighted in red. Can you run the macro in the workbook and then upload it, i cant figure out if i'm doing something wrong or not.

Thanks

Hi

OK, here it is.

rylo

Thanks, works great. 2 questions. If i think there is a better way to go about the snack stickers(I'm thinking mail merge, as it still isnt giving me the exact layout i need), how can i edit the macro to not pull info to the snack stickers page- just delete any reference on the macro to snack stickers?), and if i update a resident on snacks which shows on my snack stickers unfiltered, when i run the macro, should it not pull that information, and update it to snack stickers filtered? I tried that, and it didnt update for me.

Hi

1) to stop the data going to the snacks sheet, then just comment out all the code starting with the line

Please Login or Register  to view this content.

2) I made a change to the data in master, and it copied across to Unfiltered, then Filtered, then Snack Stickers. Can you give me some examples of exactly what you are changing where, and what it should be when moved across the other sheets.

rylo

Tried that, and ran into compile errors etc. When I added data into AM Snack or AM Fluid for the first 2 residents, they show up in unfiltered, but nothing shows up in filtered. Do i always have to run the macro when i update information for a resident? Sorry for all the questions, really trying to understand this, and how it works, and i definitely appreciate all of your help. I will upload what i have changed for data for the first 2 residents. If you can get the macro to work without pulling to snack data page, which i think i eliminated, that would be great. I cant seem to get my file to upload for some reason.

I cant seem to upload files, so i cant show you what i did on the roster. Basically for the first and second resident under am snack and am fluid, i added 2 items(Under the master listing tab), and it pulled to the snack data unfiltered tab, but not the filtered one. So i need to figure that part out. the way the macro works is great, because it keeps the data going line 1,2,3,4 which is what i need instead of it jumping from line 2 to 45 to 128. If you can help me out this last time, i would be imensley grateful. So i need you to upload it again with the macro working, as i cant get it to work with the info you gave me.

thanks, you rock!

Hi

Ok exactly what data did you add where using the example file from post #13 as the basis. All the data from every column.

rylo

OK, i got it to work, didnt realize i had to run the macro to get it to update every time i made a change. I have been playing around with the macro, trying to get it to reference the last column in Snack Data Unfiltered with Snack Pass as the Title, and I need to add a column titled Date next to it with the filtered cells referencing the first cell under the column heading, so i placed that in H column, and have been trying to move the references in the macro to match but cant seem to. This is what i have left as an edit, can you see if i have made a mistake in this? I am getting an "extract has a missing or illegal field name" And as you said before, i can remove everything after
OutSh.Activate right? When it gives me the error it highlights the last row with all the references in it.
Sorry for being a pain in the ***.

Sub bbb()
Dim OutSH As Worksheet, DataSH As Worksheet, StickersSH As Worksheet
Set OutSH = Sheets("Snack Data - Filtered")
Set DataSH = Sheets("Snack Data-Unfiltered")
Set StickersSH = Sheets("Snack Stickers")

DataSH.Range("I1:J1").Value = DataSH.Range("F2").Value
DataSH.Range("I2").Formula = "=""<>""&0"
DataSH.Range("J2").Formula = "=""<> """
DataSH.Range("A:H").AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("I1:J2"), copytorange:=OutSH.Range("A1:H1")
OutSH.Activate

Hi

Please Login or Register  to view this content.

You should be referencing the heading row, not a data row. So it should be F1, not F2. Unless you have changed your structure that is.

Also, can you go back and edit post #24 and put code tags around the code snip.

rylo