Solved

Populate cells with formulas

Posted on 2014-07-18
5
135 Views
Last Modified: 2014-07-18
Expert,
In automating this workbook for the client, I've been ask to populate formulas from the attached worksheet to other cells in the same workbook labeled "Consolidated". The range to be populated in the "Consolidated" worksheet would be C6:U17. For example, in cell C6 the formula needs to be ''Jan 14'!B37, cell D6= 'Jan 14'!C37, cell E6 = 'Jan 14'!D37. This is the pattern for each month.
Currently, they have 15 Projects for 2014. Next year, they may want to add another year in the same work book and update the formulas in the Consolidated worksheet. Adding the next year worksheet with the correct tabs labeled has been solved. If I can get a macro that updates the months in the Consolidated for the first year as attached I know I can use that for upcoming year(s).
If I'm unclear, please advise. Thanks to all.
Consolidaed-with-Formulas.xlsm
0
Comment
Question by:Frank Freese
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40205804
Could you do it in manually ? If so, using Excel "Record macro" function to record all
your action that you did previously and manually. The macro recording  function
will save all your action into one macro, and then you can copy the VBA code from
the macro for your reference to start your VBA macro program or writing.

It is video  for Excel recording macro tutorial at
http://www.youtube.com/watch?v=nvWpFdo7EO0

Duncan
0
 

Author Comment

by:Frank Freese
ID: 40205812
I have thought of that but that would mean 228 times. However, maybe just doing three or four I could then adjust the code for the complete macro...
0
 
LVL 13

Accepted Solution

by:
duncanb7 earned 500 total points
ID: 40205831
Just start small example first for 1 or 2 times Excell cell operation, and then get the recoded VBA code that will help to start your code writing easily  and that also help other experts to assist your to finish your 228 time target or complete issue if you can provide some preparation work vba code, that also help on communication easily and fast during question reply and answer on this thread.

If the 1 or 2 times operation is similar to 224, you just change those variables setting
on the recorded VBA macro, for example,I think next time you will ask how to
program the workbook name as variable on VBA, that will help to narrow down the
issue and  reduce your project time.



Duncan
0
 

Author Comment

by:Frank Freese
ID: 40205834
That's exactly what I'm working on now.
0
 

Author Closing Comment

by:Frank Freese
ID: 40205842
thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now