-2

The goal is to completely fill sheet1 with all of the data from sheets 2,3,4,etc.

The source sheets may have variable number of rows filled with data, however, there are no gaps in the rows (i.e. the last row with data in it is the last row of data in that sheet) There are a fixed number of sheets in the workbook.

  1. Fill the data into sheet1, starting at row 1, from sheet2, row 1.
  2. Fill the data into sheet1, starting at the next open row, from sheet3, row 1.
  3. Repeat until all sheets are copied into sheet1.

Basically, all of the other sheets will be used as workspace to then create a final continuous sheet that will be imported into another program.

I may have done what I needed by doing the below code in a macro and using Sheets("sheetname").Select for each sheet. I would rather do this in a For/Next loop to get rid off all the redundant lines though.

Sub CreateImport()

Sheets("Import").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("A2").Select
Sheets("IOAccess").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Import").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Sheets("MemoryDisc").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Import").Select
ActiveSheet.Paste
etc...
Community
  • 1
  • 1
Midnight412
  • 1
  • 2
  • 1
    SO is not a code writing service. Try fivver.com. Or if you have code you have written and having trouble getting it to work, paste it here and someone can assist you. – mrbungle Jan 14 '16 at 16:40

2 Answers2

1

Combining data from multiple Sheets in a single Workbook to a single Sheet for use with a different program is a task that VBA is suited for nicely.

The code below is heavily commented to explain what is happening in each step, though the LastOccupiedRowNum and LastOccupiedColNum functions are not. Identifying the last occupied row / column is foundational to VBA programming and is explained masterfully here: Error in finding last used cell in VBA

In a nutshell, the two most important takeaways are the Workbook.Worksheets property and the Range.Copy method.

Option Explicit
Public Sub CombineDataFromAllSheets()

    Dim wksSrc As Worksheet, wksDst As Worksheet
    Dim rngSrc As Range, rngDst As Range
    Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long

    'Notes: "Src" is short for "Source", "Dst" is short for "Destination"

    'Set references up-front
    Set wksDst = ThisWorkbook.Worksheets("Import")
    lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below
    lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below

    'Set the initial destination range
    Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

    'Loop through all sheets
    For Each wksSrc In ThisWorkbook.Worksheets

        'Make sure we skip the "Import" destination sheet!
        If wksSrc.Name <> "Import" Then

            'Identify the last occupied row on this sheet
            lngSrcLastRow = LastOccupiedRowNum(wksSrc)

            'Store the source data then copy it to the destination range
            With wksSrc
                Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
                rngSrc.Copy Destination:=rngDst
            End With

            'Redefine the destination range now that new data has been added
            lngDstLastRow = LastOccupiedRowNum(wksDst)
            Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

        End If

    Next wksSrc

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last row
'OUTPUT      : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
        End With
    Else
        lng = 1
    End If
    LastOccupiedRowNum = lng
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last column
'OUTPUT      : Long, the last occupied column
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedColNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Column
        End With
    Else
        lng = 1
    End If
    LastOccupiedColNum = lng
End Function

Using the macro recorder like you seemingly did above is a great way to start learning about VBA, but you'll quickly hit a wall when it comes to loops.

You can see this code in action and learn more about the included concepts in this 4-minute walk through: https://www.youtube.com/watch?v=vbC2lGLFXS0&feature=youtu.be

Community
  • 1
  • 1
Dan Wagner
  • 2,693
  • 2
  • 13
  • 18
0

Try in Sheet1 cell A1

=query({Sheet2!A:Z; Sheet3!A:Z; Sheet4!A:Z}, "where Col1 <>'' ")

In case col A holds numerical data change the last part to "where Col1 is not null".

See if this works ?

JPV
  • 26,499
  • 4
  • 33
  • 48