Merge 3 Sheets Into One / Import To Another Workbook For Use

[jonsharman]

Hi,

I am currently using Excel to create a profit driver spreadsheet than can be used to enter the selling price of an item in stock and then will return the profit and some other pertinent information but I need some help to make this process easier for the end user.

My problem is that the stock is updated overnight and saved into a workbook that can be downloaded from our server and annoyingly it is split into three sheets on the workbook rather than just one; I have asked our IT guys if they can do it as one sheet and they say they cant as it is the same for every division. The good news is that file name remains constant, all of the column headers are in the same order and are stored in row 5 (location, price, bonus, VAT status etc) on each sheet and the data I want to use starts at row 6 in each sheet. However depending on the level of new stock overnight determines how much data there actually is in each of the sheets - some days the data may finish at row 27 on Sheet1, 38 on Sheet2 and row 521 on Sheet3 but other days it may end at row 81 on Sheet1, 69 on Sheet2 and 932 on Sheet3. There are other sheets in the stock workbook but I do not need to worry about these.

I have two questions:

Query one is can I create a macro that locates the stock download workbook in a directory, copies the column headers from row 5 of Sheet1 into a STOCK sheet in my exiting workbook then copies all of the data from row 6 onwards from the three sheets in the stock download workbook and displays a quick 'Successful' message when done - i.e merges all three sheets into 1 master stock sheet.

Query two is I am using INDEX / MATCH to help find the data in the STOCK sheet via the stock number but can I change this so that if the user puts in either a part number or stock number it returns the correct result; at the moment I have a little VLOOKUP to the side so that if the user only knows the part number it will return them the stock number and they can then type this into the correct field to return the right information. Currently if they put a part number in obviously it just gives an error but most keep forgetting it must be stock number!

Any help would be appreciated!

Thanks

J

 

[Oeldere]

for your first question you could use a macro (similar) like this.

Sub integratie_Oeldere_revisted_vs3()

'I got a lot of help from AB33, to get this code working; thanks for that AB33.

Dim wsTest As Worksheet

'check if sheet "Summary" already exist

Const strSheetName As String = "Summary"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

With Sheets("Summary")
    .UsedRange.ClearContents
    .Range("A1:F1").Value = Array("sheet", "location", "price", "bonus", "VAT status", "etc")
    For Each sh In Sheets
        With sh
            If .Name <> "Summary" And .Name <> "Output" And .Name <> "PivotTable" And .Name <> "Desired output" Then
                 LR = .Cells(.Rows.Count, 1).End(xlUp).Row
                If LR >= 2 Then
                   rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
                   NR = Sheets("Summary").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                   If rng > 0 Then
                    Sheets("Summary").Cells(NR, 1).Resize(rng) = .Name
                    Sheets("Summary").Cells(NR, 2).Resize(rng, 5) = .Range("A2").Resize(rng, 5).Value
                  End If
               End If
            End If
        End With
    Next
    On Error Resume Next
  '  .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
    .Columns("A:Z").EntireColumn.AutoFit
End With
End Sub

 

[Oeldere]

Maybe you can achieve your second question also with an pivot table.

But without seeing your data, it is hard to tell.