On Monday, April 3, 2017 at 8:26:21 AM UTC-5, GS wrote:
Paste this into a standard module:
Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.
Option Explicit
Sub ConsolidateSheets()
Dim lRow&, lRow2&, vData, vSh, wsTgt As Worksheet
Dim CalcMode As XlCalculation
'Get a ref to the target sheet
Set wsTgt = ThisWorkbook.Sheets("Consolidate")
wsTgt.Cells.ClearContents: lRow2 = 1
'EnableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False
CalcMode = .Calculation: .Calculation = xlCalculationManual
End With 'Application
'Transfer data from the other sheets to the next empty row
On Error GoTo Cleanup
For Each vSh In ThisWorkbook.Sheets
If Not vSh = wsTgt Then
lRow = vSh.Range("A" & vSh.Rows.Count).End(xlUp).Row + 1
vData = vSh.Range("A1:BB" & lRow)
wsTgt.Cells(lRow2, "A").Resize(UBound(vData), UBound(vData, 2)) =
vData lRow2 = lRow2 + UBound(vData) '//set next blank row
End If
Next 'vSh
Cleanup:
Set wsTgt = Nothing
'DisableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False: .Calculation = CalcMode
End With 'Application
End Sub 'ConsolodateSheets
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Hi Garry
Thanks for the quick response. Unfortunately, you are probably going to tell
me I have no business try to do this....... I don't know what you mean by
'Standard Module' or 'Project Explorer pane'.
Paste this into a standard module:
Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.
I have a folder in my google drive with three forms which are feeding into
one workbook. In the workbook, I have three tabs/sheets of data feeding in
from the forms and I have made an additional tab/sheet called Aggregate where
I would like the data from the three sheets/forms to feed into from the
individual sheets. I will continue to add more forms, hence more tabs/sheets
of data will be added to the workbook on a continual basis.
Does this make sense and are you able to clarify Module and Project Explorer
pane?
I so much appreciate your input.
Ok then, you'll need to change the name of the sheet in the code by editing
this line as shown...
Set wsTgt = ThisWorkbook.Sheets("Aggregate")
Code goes in the Visual Basic Editor (VBE) component. You can open it using
keyboard combo 'Ctrl+F11'.
The 'Project Explorer' pane looks like the folderview pane of the Files
Explorer. In there you'll see your workbook listed; -right-click its name or
any item under that to access a popup menu. Click 'Insert' to display a
submenu, then click 'Module'.
You should now have a window titled "<yourfilename - Module1 (Code). This is
where you paste the code.
To run the code press 'Ctrl+F8' and select the macro in the listbox; -then
click 'Run'. That's it!
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion