Macro copying values and sheet names of all activated sheets

[Rosixks]

Hi!
I am desperate to find a solution for my Workbook, for which I need to create a macro. My problem is that i don't really have much experience with macros. I really hope you can help me! That's the situation:

I am creating a summary sheet for a document with over 100 sheets with the following characteristics:
- Not all the sheets are activated
- All the sheets have different names
- All the sheets are structured in the same way

I need a macro to copy the values of a cell range from all the activated sheets and paste them in the summary sheet. It should be able to:

- Copy values of I9:N9 from all the sheets and paste them in...
D5:I5(values of sheet 1)
D6:I6(values of sheet 2)
D7:I7 (values of sheet 3)
D8:I8 (values of sheet 4)
D9:I9 (values of sheet 5)... and so on

- Copy sheet name of activated sheets and paste them on a list on summary sheet starting at C5

The summary sheet already exits and it's called "RESUMEN" (I already have some other information and formulas running on "RESUMEN"). I am interested in ...
...Copying only cells values because they are actually formulas
...Copying only from activated sheets, since I have some inactive sheets that I use as drafts for other macros

I would be really glad if someone could help me! Thank you so much!!!!!!!!!

 

[Smitty]

You can start by recording a macro for a few of the sheets. Looping the code through all of the sheets isn't too difficult at all, but you'll need to clarify what you mean by "Activated" sheets.

Here's an example of copying from all sheets to the RESUMEN sheet:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> ws.Name <> "RESUMEN" <SPAN style="color:#00007F">Then</SPAN><br>                ws.Range("I9:N9").Copy Sheets("RESUMEN").Cells(Rows.Count, "D").End(xlUp).Offset(1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[Rosixks]

Hi Smitty,

Thanks for your help, by activated I mean "visible".
I was trying to run the macro, but somehow is not working...can that be because the copied cells are not having numbers but formulas?

Thanks again!!!

 

[Smitty]

That's entirely possible. To get around that you can use the PasteSpecial Values method instead of a straight paste.

You can get the syntax for it by recording a macro copying and pasting as values, then substitute it in the existing code.