Need VBA to copy data from looped sheets into summary sheet

Hey Guys,

I'm not too familiar with loop commands, and not even sure if looping worksheets to get data and pasting them into the summary is the best way for what i want to achieve.

I have a workbook with several sheets, the number of sheets will be different depending on the source.. However, the layout will be consistent (ie red Combined sheet in the middle somewhere, followed by the summary)

I need a macro which can extract certain information from selected cells from the various sheets to the right of the summary sheet, and paste them into the appropriate cells in the summary sheet in a descending order (ie, not writing over the previous entry)

I've attached a sample for reference. testhelp.zip

Basically, the summary sheet should be populated with the corresponding data in the sheets that follow it (blue tabs).
The first entry has been entered manually into the summary tab as an example.

if anyone can help me write a macro that can loop through the sheets and fetch the info into the summary sheet, that would be absolutely fantastic!

Cheers,
Caleb

Hi everyone,

Can someone help me with a loop command to be used within the following macro at all?
I've written this macro, and it does what i need it to do; however I still need to manually select the "OU" so the macro can finish.

At the moment, The macro asks for which OU to generate the report for. The OU is displayed only in column "CF".. The length of this column can range from 2 values through to about 100, starting at cell CF2, going down.

I believe there is a way to loop the following, which would cycle through the list, without having to enter each and every OU value, which is what is required at the moment - takes forever! I am somewhat clueless about how to use the loop function properly, and have tried several times but not making much headway.

Please Login or Register  to view this content.

I would imagine a range would need to be declared, and then a loop to cycle through the list. I also don't know how to put in an appropriate error handler, which will exit out of the macro when something goes wrong.. At the moment, I can't stop the macro from running when I accidentally enter the wrong OU.. however with a working automated loop that can cycle through the OUs, this probably wont even be required.

Any assistance on this would be much appreciated... I still have about 90 sheets of raw data to go through, each with varying numbers of OUs needing to be filtered and summarized. I'm thinking days of entering OUs.. !!

On a side note, the macro is taking long to run, even after i turned off the screen updates. I suspect that it is due to the macro calculating the the entire column whenever the red codes are used. This is needed as the raw data can extend to varying lengths, and this is used to make sure no data is missed in the calculations. is there another way for writing a formula functions that can determine where the last data cell is within the column in order to be used in the calculation, ie not have to total the whole column every single time?

Please Login or Register  to view this content.

Originally Posted by abousetta

OK. Obvioulsy you are taking a crash course in vba and there is no shame in that. I think we will be able to help you with a little guidance from you on what you want. Let's start with the basics:

.....

I have to go now but post back and we can work on improving your code further.

abousetta

Awesome !!
Appreciate any advice, tips and guidance you can offer. Please keep em coming!
Simple shortcuts to make the codes tidier and faster are always a good thing!

Thanks!

Hey guys,

I'm still having trouble with looping through a column of unique values.
At the moment, I have determined the unique values in all my data into a separate column, where I can then bring up an input box that allows me to filter all my data based on the required OU for my report.

Basically, I need to filter the data to show only information relevant to the OU, then either export it to a new sheet, or calculate, copy and paste the totals to a new sheet.

The current code works (as per previous post); however I still need to rely upon manually entering the OU (which I had another macro find and show by extracting the unique values - The other macro was designed to filter the state and BU).

I need a loop in place to automate the filtering, removing the need to manually enter an OU altogether. Is there a way this can be achieved?


How do I replace this...

Please Login or Register  to view this content.

with a loop function that will filter field 15 with all the OUs listed in column CF.. Essentially, I want excel to perform the task of running my entire code for all the OUs that are listed in a column, rather than having to manually enter each OU to generate the report.

Anyone? Solving this issue will literally save me hours of repeatedly entering OUs into an input box.