Log In
Register
Home
Active Member
November 14, 2020
Offline
Hello,
My name is David... I need a VBA script that will autofill data from a single column from multiple worksheets into one Summary worksheet.
My worksheet has multiple "Bin" worksheets for inventory cycle counts. Each worksheet represents a different bin on our shelf. Our cycle counters scan the serial # for each part into Column "G" of each worksheet. For the sake of size, I created a small sample worksheet, but my actual worksheet can have up to 20-25 worksheets representing different bins. Each with an identical setup utilizing column "G" for their scans.
What I need is to have a Summary worksheet that will autofill each serial # scan from each of these worksheets so that I don't have to copy/paste from each each worksheet, or create a formula to vlookup (changing the worksheet source every 600+ lines). There will be other worksheets on this file containing a Master Report Download that I don't want included in the autofill.
& not to throw a wrench into the mix, my worksheets usually have the names of each bin, and not "Bin (1)", "Bin (2)", "Bin (3)", etc. However, if I have to keep the worksheets named in this fashion, that's not a big deal, but if there was a way to use my custom names for each one, that would be great.
Any help would be greatly appreciated!!
Thank you,
David G.
Trusted Members
December 20, 2019
Offline
You just want to copy all the data in the various column G's in to one long column?
Sub ConsolidateG()
Dim ws As Worksheet
Dim NextSummaryCell
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Master Download" Or ws.Name = "Summary" Then
GoTo skipws
End If
NextSummaryCell = Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("g5:g" & ws.Cells(Rows.Count, 7).End(xlUp).Row).Copy Worksheets("Summary").Range("a" & NextSummaryCell)
skipws:
Next ws
End Sub
Active Member
November 14, 2020
Offline
Thank you so much!!
Yes! That's correct... and this is exactly what I'm looking for... However, I noticed that if I have to update the list by adding a serial # or 2 to one of the bins, I can't go back and just click the button to update... it re-adds all of the tabs again, duplicating the each entry.
Is there a way that would allow me to add an update to the list (scanning for new entries) without re-adding all of the tabs again? Just adding whatever update was added to one of the tabs.
Thank you in advance!
David G
Trusted Members
December 20, 2019
Offline
It add more complexity to the macro, but yes it can be done.
Wouldnt it just be easier to delete the data in master download and run it again?
Does it need to check each sheet for a new entry or just add the data from a new bin?
1 Guest(s)
