Combine worksheets containing survey data
Combine online survey data, column wise, from different worksheets containing aligned data – Obsolete
Routine to select data received from online survey programs (ie confirmit) and to cut and paste that data into a single worksheet named ‘Original Data’.
Historically, some survey software, faced with limited columns, would split data into multiple worksheets. This routine was used to collate that data into a single worksheet. It moves through the worksheets in the selected workbook, copies the data from each sheet and pastes it into the workbook containing the routine which contains a worksheet named ‘Original data’.
This routine is obsolete unless working with historical data output. A lot of assumptions were used. Namely, the data output files has not been modified. Few validity checks were included.
To use: This routine is run from the ‘master’ workbook where the data is to be moved to.
The user is prompted to select the workbook where the desired output is. The routine then moves through the worksheets, copying the data into the single ‘master’ worksheet (The master worksheet needed to be named ‘Original Data’.
‘
NOTE: A lot of assumptions are used… Records are aligned, no extra sheets have been added etc.
Option Explicit Sub CombineSheets() 'Routine to select data received from confirmit and to cut and paste that data into a single worksheet ' Historically, some survey software, faced with limited columns, would split data into multiple worksheets. ' This routine was used to collate that data into a single worksheet 'Overview: This routine is run from the 'master' workbook where the data is to be moved to ' The user is prompted to select the workbook where the desired output is. The routine then moves through the ' worksheets, copying the data into the single 'master' worksheet. ' NOTE: A lot of assumptions are used... Records are aligned, no extra sheets have been added etc. Dim LastRow As Integer, CurrWS_LastCol As Integer, MasterFirstCol As Integer, MasterLastCol As Integer Dim LastCell As Range Dim WS_Count As Integer Dim Counter As Integer Dim CurrWB As Workbook, MasterWB As Workbook Dim CurrWS As Worksheet, MasterWS As Worksheet 'Master workbook is the workbook to put the data into (ie the collated data) Set MasterWB = ThisWorkbook 'Select the workbook to collate (ie the data received from confirmit) Application.Dialogs(xlDialogOpen).Show 'CurrWB is the workbook with the original data received from confirmit Set CurrWB = ActiveWorkbook 'MasterWS is the worksheet to put the data into Set MasterWS = MasterWB.Worksheets("Original Data") 'Clear current contents of master worksheet MasterWB.Activate MasterWS.Activate MasterWS.Cells.Select Selection.Clear 'Go through each worksheet of the received data and cut and paste into the MasterWS (Original Data) For WS_Count = 1 To CurrWB.Worksheets.Count ' Set CurrWS = CurrWB.Worksheets(WS_Count) Set LastCell = CurrWS.Cells(1, 1).SpecialCells(xlCellTypeLastCell) LastRow = LastCell.Row CurrWS_LastCol = LastCell.Column If WS_Count = 1 Then MasterFirstCol = 1 MasterLastCol = CurrWS_LastCol End If CurrWS.Activate CurrWB.Activate CurrWS.Select CurrWS.Range(CurrWS.Cells(1, 1), CurrWS.Cells(LastRow, CurrWS_LastCol)).Copy MasterWS.Activate MasterWS.Range(MasterWS.Cells(1, MasterFirstCol), MasterWS.Cells(LastRow, MasterLastCol)).Select MasterWS.Paste Destination:=Selection MasterFirstCol = MasterLastCol + 1 MasterLastCol = MasterLastCol + CurrWS_LastCol Next WS_Count MasterWS.[A1].Select CurrWB.Close End Sub