specific named tab combining macro

**jutah** · 01-19-2010, 06:41 AM

I would like to edit this code so that the macro only copies certain tabs (which I specify the name of) from the selected workbooks & onto the sheet named ‘data’

Sub workbook_combine_values()

Dim pasterow As String

mainsheetname = ActiveWorkbook.Name

MsgBox ("Please select spreadsheets to combine")
filestoopen = Application.GetOpenFilename(MultiSelect:=True)

responseval = MsgBox("Do you want to leave the combined spreadsheets open?", vbYesNo)

Worksheets("Data").Select
Range("A1").Select

'open workbooks
For Each w In filestoopen

Workbooks.Open Filename:=w
copysheetname = ActiveWorkbook.Name
    
    'copy and paste sheets
    For Each sh In Worksheets
    
        sheetnumber = sh.Index
        
        something = Worksheets(sheetnumber).UsedRange.Rows.Count + 1
        
            Worksheets(sheetnumber).UsedRange.Copy
            Workbooks(mainsheetname).Activate
            pasterow = Workbooks(mainsheetname).Worksheets("Data").UsedRange.Rows.Count + 1
        If pasterow = 2 Then
            pasterow = 1
        End If
            Workbooks(mainsheetname).Worksheets(2).Range("A" & pasterow).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            
            Workbooks(copysheetname).Activate
    
    Next sh

If responseval = 7 Then
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End If

Next w

Workbooks(mainsheetname).Activate

End Sub

**teylyn** · 01-19-2010, 07:38 AM

Hi jutah,

welcome to the forum, however ....

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I'll add the code tags for you this time only and I'll expect you to use them from now on.

**jutah** · 01-19-2010, 11:16 AM

Thank You. I'm trying to combine hundreds of worksheets but only need one conventionally named tab from each. My issue is that the combined data exceeds 65000 rows so I need a way to pick only the data I require.