VBA: Creating a master sheet from multiple worksheets with undefined entries

Question

Please help, I am in need of it desperately and any help is appreciated.

I am creating a summary sheet, which will bring in 5 columns from all the sheets.

The sheets may have extra columns but I am only interested in bringing in specific variables to the summary sheet from each sheet. Also, the sheet will have a column named milestone, which is of major interest, so I am only interested in data for the 5 columns for the points where the milestone is not blank.

Also, each sheet may have multiple data points, independent of number is other sheets.

The summary sheet should include all points (all milestones and relevant data for each point from sheets) and be auto-populated as each sheet is filled out by each employee.

So far, I have this but it does not seem to be working, I am very new to VBA, thanks a ton:

Sub MakeSummary()

'J stands for rows in summary2 sheet
'I stands for sheet number

Sheets("SUMMARY2").Select
'Range("A1:D60").Value = ""
J = 4
For I = 4 To Sheets.Count
    A = Sheets(I).Name
        If (Sheets(A).Range("A1").Value = "") Then GoTo 10
        x = 3
            For Each Worksheet In ThisWorkbook.Sheets

                Do Until Cells(x, 1).Value <> ""
                    Range("A" + Format(J)).FormulaR1C1 = "='" + A + "'!R4C1"
                Loop

                ''Do While Cells(x, 1).Value <> ""
                    ''Range("B" + Format(J)).FormulaR1C1 = "='" + A + "'!R4C2"
                ''Loop

                Do Until Cells(x, 1).Value <> ""
                    Range("B" + Format(J)).FormulaR1C1 = "='" + A + "'!R4C3"
                Loop

                Do Until Cells(x, 1).Value <> ""
                    Range("C" + Format(J)).FormulaR1C1 = "='" + A + "'!R4C4"
                Loop
            Next Worksheet
    J = J + 1
10:
    Next I

End Sub

Answer 1

EDIT: Adjusted the design a bit (removed the sector column from each source sheet as that info is available from Sheet.Name), try this out:

Option Explicit
Sub CombineDataSheets()

Dim Summary As Worksheet, Sheet As Worksheet
Dim MonthCol As Long, LastSummaryRow As Long, _
    LastRow As Long, Index As Long
Dim Source As Range, Target As Range

'set references up-front
Set Summary = ThisWorkbook.Worksheets("SUMMARY2")
MonthCol = 1
LastSummaryRow = FindLastRow(Summary)

'loop through sheets and write info back to summary
For Each Sheet In ThisWorkbook.Worksheets
    'write out all data except sector
    If Sheet.Name <> "SUMMARY2" And Sheet.Name <> "Summary" _
        And Sheet.Name <> "Milestone Types" Then
        With Sheet
            If .Cells(4, MonthCol) <> "" Then
                LastRow = .Cells(.Rows.Count, MonthCol).End(xlUp).Row
            Else
                LastRow = 4
            End If
            Set Source = .Range(.Cells(4, MonthCol), .Cells(LastRow, 7))
        End With
        With Summary
            Set Target = .Range(.Cells(LastSummaryRow + 1, MonthCol + 1), _
                .Cells(LastSummaryRow + 1 + LastRow, 7))
            Source.Copy Target
            'write out sector data
            Index = FindLastRow(Summary)
            While .Cells(Index, 1) = ""
                .Cells(Index, 1) = Sheet.Name
                Index = Index - 1
            Wend
        End With
        LastSummaryRow = FindLastRow(Summary)
    End If
Next Sheet

End Sub

'handy function to identify the last row in a worksheet
Public Function FindLastRow(flrSheet As Worksheet) As Long
    If Application.WorksheetFunction.CountA(flrSheet.Cells) <> 0 Then
        FindLastRow = flrSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Else
        FindLastRow = 1
    End If
End Function

Original Response: Here are some jumping off points:

Declare your variables and use Option Explicit to catch typos.

Option Explicit '<~ will save your bacon
Sub MakeSummary()

'declare variables, here's a start:
Dim J As Long, I As Long, x As Long
Dim Sheet As Worksheet
'...
'start doing stuff

Avoid .Select when you can. Some serious knowledge dropped here, study up: How to avoid using Select in Excel VBA macros

'...
Dim Summary As Worksheet
Set Summary = ThisWorkbook.Worksheets("SUMMARY2")
'...
'you can now operate on the Summary sheet by referencing it directly.
'for example:
Summary.Cells(x, 5) = "Some Text"

Look for repetition, as it's often an opportunity to refactor. You've got three Do...While loops that are all doing the same thing here, so you can combine your logic:

'...
Do Unit Cells(x, 1).Value <> ""
   Range("A" + Format(J)).FormulaR1C1 = "='" + A + "'!R4C1"
   Range("B" + Format(J)).FormulaR1C1 = "='" + A + "'!R4C3"
   Range("C" + Format(J)).FormulaR1C1 = "='" + A + "'!R4C4"
Loop
'...

Steer clear of GoTo unless it's ABSOLUTELY necessary. A tiny mistake in a GoTo will eventually lead you to pull out your hair and is also a major code smell.