VBA Combing Multiple Worksheets

[fearnoeveil]

Hi. I have about 450 Excel workbooks containing order information. In column A there are inventory numbers and in column H there are the quantities ordered. The number of rows containing the information changes from order to order but never more than 10.

Half way through the 450 we started using a new form in which case the inventory stock number is merged into c,d, and e and the quantities are in merged cells o,p. In some cases there is only the information in the first sheet, in other cases, the information goes all the way to the 12th sheet. Maybe more.

I need to extract all the inventory numbers and quantities from each workbook and sheets within. Is there a way to create a macro to do this? I am not against moving the old forms into a new folder and keeping the new forms in a seperate folder.

ALso, I have been playing with codes all morning and I don't know very much about VBA. I can record the heck out of a macro but creating one from scratch is greek to me. Please provide basic instructions on that as well.

I tried going to the developer tab. Clicking on Visual Basic then clicking insert module. That just inserts a module into Macros I have already recorded. I can't figure out how to get around that.

 

[harshab6]

Hi,
Do you want to merge 450 workbook into single workbook?
or all the worksheets in workbook to a single sheet?

 

[fearnoeveil]

Hi,
I need to merge several sheets from the 450 books into one sheet in a new workbook if possible.

It seems complicated to me but if there's a way to simplify it, I have no objections. I just don't want to open, copy and paste all those lines.

Thanks so much for taking the time to review this.

 

[harshab6]

Hi,
In order to extract 450 books in to single book kindly use this code
code in order to merge workbook

ub Mergeworkbook()
Z = Application.GetOpenFilename(MultiSelect:=True)
'Open loop for action to be taken on all selected workbooks.
For x = 1 To UBound(Z)
Set WB = Workbooks.Open(Z(x))
WB.Sheets.Copy Before:=ThisWorkbook.Sheets(1)
WB.Close False
Next x
End Sub

in order to merge all this workbook to single sheets

Sub mergesheet()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.UsedRange.Offset(3).Copy
With Range("A65536").End(xlUp).Offset(1, 0)
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
End If
Next
End Sub

Kindly change the ranges as per your requirment.

Regards
Harsha