Hi everyone, just joined. So, I have a question regarding duplicating a cell on another sheet x times, then moving to the next row in the column, repeating that one the same number of times, etc. I have this, which works for the first cell if I have a formula in A1 referencing the other sheet:
Please Login or Register to view this content.
Any help would be great. I have 250ish unique cells in the same column that I need repeated 49 times all in the same column on another sheet. Thanks!
Last edited by JBeaucaire; 11-17-2014 at 08:14 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
Okay, I'll bite. I have a feeling this question has been left alone because it raises a lot of issues/concerns, and programmers do not like too many unknowns. It usually means their solution is not what the requester really wanted. So, please answer these questions so that a better understanding is reached.
1) what is your programming level? Specifically, did you write this or did someone give it to you? The short answer is you need some type of loop (For...next, do...while, etc.). Maybe that is enough to get you started.
2) why are you using an event handler (Worksheet_SelectionChange) and not a subroutine that can be called? Currently, this subroutine is run every time the selection gets changed, which seems like overkill.
3) your description talks about copying to a second sheet. Your code, which works, has no reference to another sheet
4) your description talks about going to 'the next row in the column' but your code is autofilling down a column, specifically A1:Ax. That would erase your source data. It is not clear to me what you are trying to achieve.
5) you disable screen updating and never turn it back on. Is there another routine that turns it back on or should it be in this sub?
6) I have a hunch this can be handled with just a formula. Any reason you need a macro?
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
Hi Pauleyb,
To answer your questions:
1. no real programming experience. I can record macros and am pretty good with formulas but no VBA.
2. This is code that I found, which is close to what I need
3. See 2
4. I get a dashboard report in excel that is broken out into project number and associated savings per quarter for 8-quarters per group (period, inventory, overhead, etc). There are 48 columns total. I need to be able to take this from a horizontal orientation and put it into 3 columns (project #, quarter, and savings type) for a program called Tableau to understand.
5. not sure
6. I need something I can run that is automated because I have to do this twice a week on a dashboard with ~250 projects (x the 48 rows for each)
Thanks for answering the questions. It seems you are now trying to summarize some data. Maybe a smart INDEX formula or a Pivot Table would work better. Please upload a sample spreadsheet with input data and desired output.
Pauley
I need to produce the summary tab. Any help would be great!
On your summary sheet:
A1: =INDEX('Project Attributed Table'!G:G,INT((ROW()-1)/48)+2)
B1: =VLOOKUP(A1,DynamicReport!G:CA,MOD(ROW()-1,48)+23,FALSE)
C1: =INDEX(DynamicReport!$AC$6:$BX$6,1,MOD(ROW()-1,48)+1)
D1: =INDEX(DynamicReport!$AC$5:$BX$5,1,MOD(1+8*(INT((ROW()-1)/8)),48))
Highlight A1:D1 and drag down as far as you need.
You've just saved my sanity! Thanks a ton!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks