Sorting Data With VBA. Possibly With Vlookup?

[figuare9]

I have built a tool on a different sheet, and it basically fills out 5 rows and 3 columns of data. What the tool does doesn't really pertain to anything I need to do, but I'd like to have a way to send the data that I use with the tool to another sheet in the workbook.

So data in C13:E17 is the range the tool will fill in the "Tool" sheet6. I'd like to make a command button and send the data to another sheet's range example: Sheet6 H4:J8. I'd also like to send different data from the tool to other locations as well. Example: Sheet6 H19:J23.

The thing that confuses me, is that I would like the data to be able to sorted in Sheet6. So I know how to send the data via command button VBA, but I only know how to send it to a cell reference. However, if I start to sort data in say Sheet6, it's going to start messing it up correct?

So if I hypothetically sent the values from the range on the "tool" page to the other sheet, and then I sorted the other sheet (because there is other data there other then what I want to send from the tool) the cells would be wrong then right?


Example..

This is the result of the tool. There could be up to 5 sections here with data.

When I click the Update button, I'd like it to update in the other sheet..

Every 5 rows is a different Item. So for instance, Item# 1500 has 5 sections, 1499 has 5 sections, etc, etc..

So when I sort this data, I need it to sort WITH the Item#.



Anyways.. I guess I could use some help with sending the data from the tool to each item#, and also maybe some help with sorting the data after it gets sent over. I'd like to have a few sorting options. By Item#, another sorting method by SAP# (which is blacked out), and by length for sure. But when I sort it, I want it to take the 5 rows associated with the item# with it.

 

[C with no eyes]

Can you put all the data onto the same row instead of separate rows? I'm guessing not (if you're exporting, which it sounds like you are). It would certainly be the easiest solution.

Another workaround (one I use all the time) is to just create a sheet called Workings. Have the data pulled through onto a single row, sort it by whatever. Then have another section of workings that puts it back into the correct format to be copied and pasted (paste values is normally best) to wherever you want it.

When using a Workings sheet I find that I use INDEX() and MATCH() with ROW() and COLUMN() a lot. These relate to the cells own row and column so the data will always match whatever is on the equivalent row or column. It means rows can be inserted & deleted but the workings will always show what is on the referenced sheet i.e. #REF should not occur.

I am assuming you know how to use these functions and how to record a macro. If not, then I can provide a more detailed example.

 

[figuare9]

Well, I actually put all the data into one row. I hate it, but I know it'll make it a lot easier in the end..

Now, i guess since I'm using one row for the data, It'll help me sort it..

However, now I'm just a little bit confused.. If I program the button to send the tools results to my worksheet that has all the data and THEN I sort it, what happens if I change the data from the tool and send it again? How do I get it to check that? If that makes sense..

If I click update, and say I program the button to say ** Worksheets("Sheet5").Range("H4").Value = Worksheets("Sheet6").Range("D13").Value ** and then I sort in my data overview and click that button again, it's going to completely screw my data table up..

Would this be best to somehow add a match for the Item # and use offsets for this data entry them to accommodate sorting?

I really can't use activecell function here because it's in another sheet, and it would be a huge pita for the end user. (probably)

 

[C with no eyes]

To be honest, I record most of my macros by hitting record then tidying up the 'Select's wherever possible! Hopefully someone else will be able to help you further.

Sorry I couldn't be of any more help.