Macro copying values and sheet names of all activated sheets

**Rosixks** · 09-06-2011, 01:34 PM

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!!!!!!!!!

**JBeaucaire** · 09-06-2011, 08:21 PM

If by "inactive" you mean "hidden", then this should do it:

Option Explicit

Sub CreateSummary()
Dim ws As Worksheet, wsSUM As Worksheet, NR As Long

Set wsSUM = Sheets("RESUMEN")
NR = 5

    For Each ws In Worksheets
        If ws.Name <> "RESUMEN" And ws.Visible = xlSheetVisible Then
            wsSUM.Range("C" & NR) = ws.Name
            wsSUM.Range("D" & NR).Resize(, 6).Value = ws.Range("I9:N9").Value
            NR = NR + 1
        End If
    Next ws

End Sub