LinkBack URL
About LinkBacks
Any help is appreciated!!!!
I would like to make this run faster and more efficiently. It goes on to 16 more sheets with different cells and columns.
Please Login or Register to view this content.
Any help is appreciated!!!!
I would like to make this run faster and more efficiently. It goes on to 16 more sheets with different cells and columns.
Please Login or Register to view this content.
Last edited by jeffreybrown; 03-12-2020 at 09:06 AM. Reason: Please use code tags!
It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Please see the yellow banner at the top of this page.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
I would like to simplify the macro. I takes to long to run. I thought there might be a different way to do this. I want to enter data on the entry sheet, then run the macro to copy the date to the other sheets.
Hi Justin,
Did you mean to have the Entry Sheet words in column A match exactly the worksheet names? You have a space between "Location" and "1" in column A but not on the worksheet names. What does your read data look like?
Last edited by MarvinP; 03-12-2020 at 11:06 AM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
That may just be a typo, the most important thing is the macro. The sheet i have uploaded is a very small sample. the full length code takes almost a two min to run.
The words in column A don't match the worksheet tab names in your example. The macro I'm writing is using the words in Col A to match the sheet names. Do they match in the real data?
No, All sheet names, ranges, and offsets will change to fit my current workbook.
That wasn't the question. Will the words in Column A be the exact spelling of the other worksheet names?Originally Posted by Justin2010
Also is there only 1 row of data below a location? Could you have many numbers following a location before the next location in column A?
Sorry, I didn't quite understand your question. Yes they will be the exact spelling. There are one to five rows below the location.(I can move all the data to one row). There can be up to 20 numbers after column A if I move it to 1 row.( currently the data is in 4 or 5 columns wide and 1 to 5 rows below. The sheet this is going on is being designed for a tablet to ease the navigation of the workbook. So i want to minimize horizontal scrolling.
There are a few ways to do this, mine looks like this:CheersPlease Login or Register to view this content.
Erwin
I started learning VBA because I was lazy
Still developing.... being more lazy...
Besides the sheet names not matching, the columns in Location 1 don't match the columns in Location 2. Have a look at the attached file. I have changed the sheet names so they match the values in column A of ENTRY SHEET and I have changed the columns in Location 2 to match Location 1. The macro is in Module1.
Ok Justin,
We are getting closer to a better question and what you have/want. The spelling of the Locations will EXACTLY match the SHEET names. If you have multiple rows of numbers under a location (In Col A) do you expect each to show up on the Location sheet. This would mean you have many rows with the same date. The question is do we OVERWRITE the data that is already on the location sheet or add a new row with that same date?
You should improve your sample file with multiple rows of data under a location row and post it again, to give us a better chance at solving your problem. I'll stop writing code until you give a better example file.
I believe with your above (new) arrangement of data the code from Eastw00d may not work.
The columns wont be the same on every sheet!
Hi Justin and welcome to the forum (I forgot that in my first post)
We are supposed to help you solve a problem. I'd start over with your data entry sheet. I'd make it only 4 or 5 columns wide. See the attached example. If you made your data entry in a better format it would be much more effective to deal with and get answers from. The way you have it now doesn't let Excel use it's power of tables of data.
I've rearranged your data into a 5 column table and done a Pivot Table from my suggestion. It would be more productive to start over with my suggestion and tell us what your major objectives are.
Hope this helps.
We really do want to help but your current data entry sheet won't get you where I think you want to go.
Marv
I have updated only the entry sheet to resemble my current sheet. I have added a second location2 row just to show how I could move my data to one row. I have not made the changes on the location1 and location2 sheet
I have 17 sheets that have different columns. I like your code, but is there any way to do it without the columns matching?
Marv, I can rearrange the entry sheet to whatever works, what seems to be the problem is that each one of my Sheets has a different column arrangement. And I am not seeing an attachment in your last post!?
Hi Justin,
This is a better example of what your data might look like. BUT - once again I think you should input your data like I suggested in my last post. Excel works in rows and columns of similar type data. I'd put a date in A1 and in row 3 starting at "A3" I'd have :
Sheet Name , Column Name, Value/Text,
What you have on your entry sheet is a "Crosstab Table" with multiple tables and data for each.
Thank you Erwin!! Your code seems to to the trick. I don't understand most of the code is it possible for you to break this part down for me so i know what it does?
For i = 1 To 6
Select Case i
Case 3, 4, 5
ws1.Cells(r, i + 1).Offset(0, 1) = .Cells(3, i + 1)
Case 6
ws1.Cells(r, i + 1).Offset(0, 2) = .Cells(3, i + 1)
Case Else
ws1.Cells(r, i + 1) = .Cells(3, i + 1)
Hi Justin,
well of course you can learn the best if you "walk" in VBE with "F8" through every line, while executing your macro.
Basically I say in this Case statement:
I have 6 cells to copy, A Cell, is a composite of a row and a column. hence the for statement.
So when I have searched for the rownumber with the find-function, I use this row r to fill the cells in the Location1 sheet, here ws1,
with each item out of the ws worksheet, by saying ... = .cells(3, i+1) , i.e. cell from row 3 and column i+1 in the Entry Sheet.
For the first two items it's no problem, they will be put in the case else part, that is all i-numbers that are not mentioned in the other cases.
Since there is a gap between- B and D - I have to offset the subsequent output with 1 , hence the line "case 3, 4, 5" where the columnumber will be accordingly 4, 5 and 6, i.e. D E and F .
And the same for the last i, again an offset extra.
I hope this is making a bit sense for you.
Cheers
Erwin
PS maybe this is nicer to read:
Please Login or Register to view this content.
Last edited by Eastw00d; 03-12-2020 at 03:51 PM. Reason: typo
Thank you Erwin!!. I have one more question before i start incorporating your code into my workbook. Comparing your code to my code in my first post, Do you think the code will execute faster a without so much processor usage?
I have never done this before, as I was always convinced that structured coding is faster, but I liked the challenge
I did some timing and my code works about 3 times faster than the original!
On top of that, if you omit the ScreenUpdating part it will go 5 times faster. In this particular case it's slowing down.
But you can see for yourself if you add this code in the beginning and at the end of the macro:
CheersPlease Login or Register to view this content.
Erwin
If you want to allocate the data according to the instructions given on Entry Sheet then try
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks