CODEWITHSUNDEEP
excelvba
user2434555
user2434555

Reputation: 1

Copy a row of only data then move on to the next worksheet if no values left

I don't know how to create this section of the code. This is a template code that I've used to copy specific values from a cell from each worksheet into one master worksheet that compiles that data usually into one row.

Sub distribute()
Dim sh As Worksheet
Dim destsh As Worksheet
Dim i As Integer

Set destsh = ActiveWorkbook.Worksheets.Add
destsh.Name = "Master"

i = 1
For Each sh In ActiveWorkbook.Worksheets
        ***destsh.Cells(i, 1).Value = sh.Range("B7:B90").SpecialCells(xlCellTypeConstants).Select***
i = i + 1
Next


   ActiveWorkbook.Worksheets("Master").Cells.EntireColumn.ColumnWidth = 30
   ActiveWorkbook.Worksheets("Master").Cells.EntireRow.AutoFit
   ActiveWorkbook.Worksheets("Master").UsedRange.UnMerge
   ActiveWorkbook.Worksheets("Master").UsedRange.WrapText = False


End Sub

I want my code to go through each worksheet in my workbook and copy the column from the range B7:B90 and stop where there are no more values within each row of each worksheet, then moves on to the next worksheet. Some worksheets have 10 cells in one row, others have 60; in this scenario my master file would show 70 cells in column A from both worksheets. The code creates a master worksheet that compiles the worksheets row B in one column.

The section I need help with has been asterisked

Upvotes: 0

Views: 32

Answers (1)

Jitendra Singh
Jitendra Singh

Reputation: 191

Your code creates the Master sheet every time which will cause the program to fail when it tries to name the sheet in the next run. Also, in your iteration through all sheets, you are reading data from Master sheet as well which might give incorrect results. I can quickly think of below code. I have used the numerical notation for rows and cells. Also since the range is constant, i looped through the range that you specified

Sub distribute()
    Dim sh As Worksheet
    Dim destsh As Worksheet
    Dim i As Integer: i = 1
    Dim sheetName As String: sheetName = ""

    Set destsh = ActiveWorkbook.Worksheets.Add

    'Taking sheet name as input from user
    sheetName = InputBox("Enter sheetname to aggregate data")

    'Checking if sheetname was entered properly
    If (sheetName <> "") Then
        destsh.Name = sheetName
        ActiveWorkbook.Worksheets("Master").Cells.EntireColumn.ColumnWidth = 30
        ActiveWorkbook.Worksheets("Master").Cells.EntireRow.AutoFit
        ActiveWorkbook.Worksheets("Master").UsedRange.UnMerge
        ActiveWorkbook.Worksheets("Master").UsedRange.WrapText = False

        masterSheetRow = 1
        For Each sh In ActiveWorkbook.Worksheets
        'Making sure that the sheet is not the master sheet while getting rows
            If (sh.Name <> sheetName) Then
                For i = 7 To 90
                    If (sh.Cells(i, 2).Value <> "") Then
                        destsh.Cells(masterSheetRow, 1).Value = sh.Cells(i, 2).Value
                        masterSheetRow = masterSheetRow + 1
                    End If
                Next
            End If
        Next
    Else
        MsgBox ("Enter valid sheetname")
    End If
End Sub

Upvotes: 0

Related Questions