none
combine workbooks with same number of columns RRS feed

  • Question

  • Hi,

    I am having multiple workbooks open. Each workbook has only one worksheet. As an example there are 20 WBs open. 5 have 300 columns, 4 have 266 columns,

    and 11 have 120 columns.

    I wish to combine all workbooks with 300 columns into a single workbook; all with 266 in another and so on.

    Then save these 3 different WBs as 3 different files.

    Please help, since I have just started learning this.

    Thanks in advance

    Friday, October 5, 2012 11:25 AM

Answers

  • Run the first macro to ensure that you actually have the number of columns you think you have, then run the second to copy the sheets into single workbooks. Change the columns count if you need to....

    Sub CheckColumns()
        Dim i As Integer
        For i = 1 To Workbooks.Count
            If Workbooks(i).Windows(1).Visible Then
                MsgBox Workbooks(i).Name & " is visible and has " _
                & Workbooks(i).Worksheets(1).UsedRange.Columns.Count & " columns."
            End If
        Next i
    End Sub

    Sub TestMacro()
        Dim Size(1 To 3) As Integer
        Dim WB(1 To 3) As Workbook
        Dim i As Integer
        Dim j As Integer
        Size(1) = 120
        Size(2) = 266
        Size(3) = 300

        For j = 1 To 3
            For i = 1 To Workbooks.Count
                If Workbooks(i).Windows(1).Visible Then
                    'MsgBox Workbooks(i).Name & " is visible"
                    If (Workbooks(i).Worksheets(1).UsedRange.Columns.Count = Size(j)) Then
                        If (WB(j) Is Nothing) Then
                            Set WB(j) = Workbooks(i)
                        Else
                            Workbooks(i).Worksheets(1).Copy after:=WB(j).Worksheets(WB(j).Worksheets.Count)
                        End If
                    End If
                End If
            Next i
            If Not (WB(j) Is Nothing) Then WB(j).SaveAs "Workbook " & Size(j) & ".xls"
        Next j
    End Sub



    Friday, October 5, 2012 2:33 PM

All replies

  • Run the first macro to ensure that you actually have the number of columns you think you have, then run the second to copy the sheets into single workbooks. Change the columns count if you need to....

    Sub CheckColumns()
        Dim i As Integer
        For i = 1 To Workbooks.Count
            If Workbooks(i).Windows(1).Visible Then
                MsgBox Workbooks(i).Name & " is visible and has " _
                & Workbooks(i).Worksheets(1).UsedRange.Columns.Count & " columns."
            End If
        Next i
    End Sub

    Sub TestMacro()
        Dim Size(1 To 3) As Integer
        Dim WB(1 To 3) As Workbook
        Dim i As Integer
        Dim j As Integer
        Size(1) = 120
        Size(2) = 266
        Size(3) = 300

        For j = 1 To 3
            For i = 1 To Workbooks.Count
                If Workbooks(i).Windows(1).Visible Then
                    'MsgBox Workbooks(i).Name & " is visible"
                    If (Workbooks(i).Worksheets(1).UsedRange.Columns.Count = Size(j)) Then
                        If (WB(j) Is Nothing) Then
                            Set WB(j) = Workbooks(i)
                        Else
                            Workbooks(i).Worksheets(1).Copy after:=WB(j).Worksheets(WB(j).Worksheets.Count)
                        End If
                    End If
                End If
            Next i
            If Not (WB(j) Is Nothing) Then WB(j).SaveAs "Workbook " & Size(j) & ".xls"
        Next j
    End Sub



    Friday, October 5, 2012 2:33 PM
  • Thanks a ton !! sir.

    regards,

    b1c11ap

    Saturday, October 6, 2012 8:14 AM
  • Dear b1c11ap,

    If any post helps you,pls vote as helpful and try to "Mark As Answer".This will help others to find the answer quickly.

    All the Best.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by b1c11ap Saturday, October 6, 2012 9:51 AM
    • Unmarked as answer by Asadulla JavedEditor Saturday, October 6, 2012 11:23 AM
    Saturday, October 6, 2012 8:21 AM
    Answerer
  • Dear b1c11ap,

    Pls mark the post as answer which helped.Bernie gave you excellent solution.

    I just being an Editor tried to attract your attention to te importance of marking a post.But defintely I did not solve the issue.

    All The best.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, October 6, 2012 11:23 AM
    Answerer