Copy info from every tab to a new tab

[jswilson64]

Howdy! First post, but I have lurked and searched these boards for a while.

I'm using Excel 2003. Trying to write a macro that will go through every tab in a workbook, copy the top row, and then paste-special-transpose that row as a column in a new tab. I have macros that do the tasks I need if I run it manually on every tab. When I add it to a macro that calls my macro for every tab, I get errors that I think are due to it trying to recursively copy or paste. Here is the code I'm using to run on every tab:

Dim wS As Worksheet
For Each wS In Worksheets
wS.Select
Call copy_headers
Next wS

When I run this (calling another macro) I get error 1004, Selection is not valid. I think it's because my copy and paste areas overlap. It's trying to run the macro on the new tab I create earlier and doesn't like it.

My question is, is there a way to do this in one workbook, or will I need to paste my values into a new workbook? Is there a way to run the macro on every tab except one called "Headers" ?

Thanks!

 

[texasalynn]

Welcome to MrExcel board...

to exclude a sheet try this

If wS.Name <> "Headers" Then
wS.Select
End If

 

[DutchKevin]

maybe not exactly waht you need,but i have this "add-a-sheet-based-on-a-template-and-make-summary-table" macro working quite nicely.
maybe with some tweeks it's suitable for your needs?
Add the first code to the "workbook", the second may go into a module.

I assume there is a "total" worksheet and a hidden "templatesheet".
I also assume that column E on the "total"sheet holds the required new sheet names.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)     'tells that any change in the workbook runs this code
Application.EnableEvents = False                'safety rule "on"
    Sheet1.Columns("A:B").ClearContents         'removes "old" data
    Sheet1.Cells(1, 1) = "sheetname"            'creates table header column A
    Sheet1.Cells(1, 2) = "Value of cells B2"    'creates table header column B
Dim i As Long, wks As Worksheet
i = 0
    For Each wks In ThisWorkbook.Worksheets
        If wks.Name <> "total" Then             'prevents using sheet
        If wks.Name <> "Templatesheet" Then     'prevents using sheet
        i = i + 1
            With Sheets("total")
                .Cells(i + 1, 1).Value = wks.Name               'tells to write the sheetnames
                .Cells(i + 1, 2).Value = wks.Range("B2").Value  'tells to write the value of cell B2
            End With
        End If
        End If
    Next wks
Application.EnableEvents = True                 'safety rule "off"
Calculate
End Sub

Sub add_sheet()
Dim k As Long
    k = 0
    k = Sheets("total").Range("E" & Rows.Count).End(xlUp).Row - 1       'count rows in Column E (-1 for header)
Dim j As Long
    j = 0
    j = (Sheets.Count)                                                  'count nr of sheets
        Sheets("templatesheet").Copy after:=Sheets(Sheets.Count)        'copy action
        Sheets("templatesheet (2)").Visible = True                      'unhide sheet
    
    If k >= j Then                  'this means that if the namelist is long enough
                                        'then rename the new sheet, elso not.
                                        
                                    'copy selected name in cell B2
        Sheets("templatesheet (2)").Range("B2").Value = Sheets("total").Range("E" & j).Value
                                    'copy selected name as sheetname
        Sheets("templatesheet (2)").Name = Sheets("total").Range("E" & j).Value
    
    End If
    
    Sheets(Sheets.Count).Activate   'jumps to the new sheet
    Cells(2, 2).Activate            'activates cell B2
'        Sheets("total").Activate   'line to jump back to total sheet
    Calculate
End Sub

Good Luck
Kevin

 

[jswilson64]

Welcome to MrExcel board...

to exclude a sheet try this

If wS.Name <> "Headers" Then
wS.Select
End If

Hey, thanks a ton! That worked (I put my Call statement before the End If as well) and I have it working. And I'm kicking myself for not figuring that out. VB is mostly foreign to me, that's my only excuse!