To copy data from 232 excel worksheets present in a single excel workbook into a destination worksheet.The row name and worksheet name should be same

Question

To copy data from 232 excel worksheets present in a single excel workbook into a destination excel worksheet present in the same workbook with the criteria that row name in the destination worksheet should be same as worksheetname.Since while naming a worksheet in an excel file we have restriction in size,so for some worksheet the names are written short but in the destiantion worksheet the names are written in fullform.So for some worksheets the data is not accumulated.How to fix this issue?Please help.I am new to VBA

Private Sub CommandButton1_Click()
Dim sheetName As String
Dim i As Integer
i = 2
Do While Cells(i, 1).Value <> ""

        sheetName = Cells(i, 1)
        Cells(i, 4) = Sheets(sheetName).Cells(190, 7).Value
        Cells(i, 5) = Sheets(sheetName).Cells(191, 7).Value
        Cells(i, 6) = Sheets(sheetName).Cells(192, 7).Value
        Cells(i, 7) = Sheets(sheetName).Cells(193, 7).Value
        Cells(i, 8) = Sheets(sheetName).Cells(194, 7).Value
        Cells(i, 9) = Sheets(sheetName).Cells(195, 7).Value
        Cells(i, 10) = Sheets(sheetName).Cells(196, 7).Value
        i = i + 1
Loop
End Sub

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-01-15T18:49:13

Try to use following code (since max length for sheet name when renaming a worksheet manually is 31 character you can restrict sheetName):

Private Sub CommandButton1_Click()
Dim sheetName As String
Dim i As Integer
i = 2
Do While Cells(i, 1).Value <> ""

        sheetName = IIf(Len(Cells(i, 1))>31,Left(Cells(i, 1),31),Cells(i, 1))
        Cells(i, 4) = Sheets(sheetName).Cells(190, 7).Value
        Cells(i, 5) = Sheets(sheetName).Cells(191, 7).Value
        Cells(i, 6) = Sheets(sheetName).Cells(192, 7).Value
        Cells(i, 7) = Sheets(sheetName).Cells(193, 7).Value
        Cells(i, 8) = Sheets(sheetName).Cells(194, 7).Value
        Cells(i, 9) = Sheets(sheetName).Cells(195, 7).Value
        Cells(i, 10) = Sheets(sheetName).Cells(196, 7).Value
        i = i + 1
Loop
End Sub

To copy data from 232 excel worksheets present in a single excel workbook into a destination worksheet.The row name and worksheet name should be same

1 Answers