Automatically transfering data after generating new worksheets

Ricky - Jan 15, 2016 at 12:52 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jan 21, 2016 at 04:23 AM

It has been a while since I have worked with code and would appreciate any help.

I have one code already working which is gathering all my information and creating a tab for each unique input on my column E. I'm looking to run an additional code which will automatically gather all the row information on the cells with the same inputs in Column E.

SO far I have this code working for the tabs, I'm at a loss for the second action.

Sub CreateTabsFromAList()
Dim MyCell As Range, MyRange As Range
Dim DeleteSH As Boolean
Dim sh As Worksheet

Set MyRange = Sheets("invval_2").Range("E2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
For Each sh In Worksheets
If sh.Name = MyCell.Value Then DeleteSH = True
Next sh
If DeleteSH = True Then
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
End If
DeleteSH = False

Next MyCell

End Sub


3 responses

Blocked Profile
Jan 15, 2016 at 07:46 PM
OK, so after the data gathering, what are you doing with it?

The reason I am asking, is because the compilation of the data matters on the type of data we are going to use, and how are we going to use it?

I do not wish to spend time writing code only for you to tell me that doesn't do what you what, or expect. The problem is you have not clearly defined your scope of work.

Please understand, we are volunteers, that can help, but cannot do the work for you.
Thank you for your response ac3mark.

I will try and provide the additional details to help with my issue.

I'm working with a program that provides me with an ugly extract of data which I need to break down for multiple locations. When the file is extracted it's one sheet of information, in the past a person would have to sort by location (column E), create a new tab for each location, filter by location and copy and paste the information into the new tab. This document would have up to 40 locations meaning 40 new sheets would have to be created and 40 sets of data to be copy and pasted over. As you can imagine not the most efficient way to complete the task.

As it stands right now with the above code I have the first step done where the new sheet is being created and labeled for any unique location. Now I'm trying to add to this code and/or create a new code to identify the new sheets name (locations) that were just created and pull all the rows of information out of the "master" extract and insert it into that new sheet.

Additional Details
- The extract document has 17 columns (Column A to Q)
- Number of rows always vary
- End result is one sheet per location with their specific information

Please let me know if you need any other information.

Thank you!
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jan 21, 2016 at 04:23 AM
Hello Ricky,

I feel that you may be trying to do a similar thing to the following thread:-

Have a look at my reply (post #2) and see if it is a possible solution to your issue. There is a link in the post to my test work book for you to try out.

Let us know how it goes.