Pulling data from a main sheet and placing it onto a newly created sheet

I have a large set of data that is broken into regions that have a number value. ie 11, 15, 18 for example. I have code that will pull certain columns from this data set and place it into a new sheet, but it is coded in a way that if new data entry is input into a row it will screw up the entire thing. I would like to know if there is a way to have an input box that will search for a region number in Column A and then pull the given columns like in the previous code and place it onto a new sheet.

sample of the original code is:

Please Login or Register  to view this content.

Why dont you attach a sample file?

Are you actually looking at transferring the various sections (groups) to individual sheets? Are you thinking of inputbox as 1 of the ways to do so?

If you run the macro once and all the sheets are created, when you run the macro again the next week, do you want the sheets to be cleared and fresh data inputted? Or should the data be copied into the next available row?

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.

I corrected the code above to make the additions. Try it and let me know.

Are you sure its a new sheet that is created and not the default sheet 2 or sheet 3 that you are seeing?

Also, when i input it into my real workbook, the data being pulled is still correct but I am missing the row 2 information (sample workbook). This could be due to the fact that in my real workbook the data doesn't begin until row 6 and the information on row 5.

Originally Posted by cbomination

Also, when i input it into my real workbook, the data being pulled is still correct but I am missing the row 2 information (sample workbook). This could be due to the fact that in my real workbook the data doesn't begin until row 6 and the information on row 5.

How are you missing row 2? Do you mean that row 5 should be copied as the header to the new sheets?

Yes, Row 2 in the sample workbook corresponds to Row 5 in my real workbook. So yea row 5 should be copied to the new sheets
EDIT Sorry my laptop froze and didnt see my post

I edited the code again. You can try it now.

Alright the header shows up, but it needs to be shifted just one column to the right. Other than that THANK YOU SO MUCH! Do you add an offset?

No i didnt use any offset. I just changed the number from 2 to 5 in this line

Please Login or Register  to view this content.

It copies the data to the new sheet cell A1.

Edit: I edited the code to also add the headers for columns A & B. I forgot about them .

Holy cow you are the best!!! Everything works great, and to answer your question earlier about that sheet being created with nothing on it. It is not sheet 2/3, it comes out to be "Sheet 45" whenever I run the macro. Its a very minor problem, it just brings up a stop in the code.

Edit: This is just for further understanding. If I want to add more columns that were to be pulled into the other sheets, how would I do so?
example
.Range("R5:T5").Copy Worksheets(sname).Range("F1")

would that copy columns R-T and place it starting at column F?

I ran the macro on your sample sheet that you provided and i do not see it. Is it possible for you to upload the copy that you are using?

Sure, give me a sec to cut out some stuff so I can get under the limit

Edit: I can't seem to get under 1MB, do you know how do get rid of a PivotTable format, but keep the data intact?
Edit: Nevermind I got it! Thank you so much for sticking with me!