Pulling Data from multiple sheet

[arijitirf]

In my excel workbook I have 50 Nos of Sheets in which column E7:E are filled up with Dates and for each date there is a numeric value in column H7:H. I want to copy those dates written in E7:E, Values written in column F7:F and and there respective value written in H7:HG along with sheet name to a sheet namely "Master" based on month search.

I've attached a explanatory file here https://www.dropbox.com/s/klh8pol7h5dlkzz/Stock Register.xlsx?dl=0

Thanks in advance.

 

[sandy666]

is that what you want?

Stock Code​	Issue Date​	Req. No.​	Unit​	Qty.​
Stock Code 1​	11/01/2018​	EL-1​	Ltr.​	100​
Stock Code 1​	12/01/2018​	EL-4​	Ltr.​	150​
Stock Code 1​	15/01/2018​	EL-6​	Ltr.​	200​
Stock Code 1​	20/01/2018​	EL-7​	Ltr.​	100​
Stock Code 1​	22/01/2018​	EL-9​	Ltr.​	300​
Stock Code 1​	31/01/2018​	EL-11​	Ltr.​	250​
Stock Code 1​	02/02/2018​	EL-12​	Ltr.​	100​
Stock Code 1​	05/02/2018​	EL-14​	Ltr.​	300​
Stock Code 1​	07/02/2018​	EL-18​	Ltr.​	250​
Stock Code 1​	08/02/2018​	EL-19​	Ltr.​	150​
Stock Code 1​	09/02/2018​	EL-21​	Ltr.​	250​
Stock Code 2​	11/01/2018​	HVAC-5​	Ltr.​	1500​
Stock Code 2​	15/01/2018​	HVAC-7​	Ltr.​	800​
Stock Code 2​	20/01/2018​	HVAC-9​	Ltr.​	100​
Stock Code 2​	05/02/2018​	HVAC-20​	Ltr.​	300​
Stock Code 2​	07/02/2018​	HVAC-23​	Ltr.​	250​
Stock Code 3​	21/01/2018​	PL-11​	No.​	10​
Stock Code 3​	03/02/2018​	PL-13​	No.​	2​
Stock Code 3​	05/02/2018​	PL-15​	No.​	4​
Stock Code 3​	07/02/2018​	PL-17​	No.​	4​
Stock Code 4​	21/01/2018​	GEN-4​	No.​	80​
Stock Code 4​	03/02/2018​	GEN-9​	No.​	150​
Stock Code 4​	05/02/2018​	GEN-11​	No.​	10​
Stock Code 4​	07/02/2018​	GEN-17​	No.​	6​
Stock Code 4​	13/02/2018​	GEN-30​	No.​	3​

try PowerQuery

[SIZE=1]let
    Source = Excel.Workbook(File.Contents("[COLOR="#FF0000"]path to the file[/COLOR]\Stock Register.xlsx"), null, true),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
    #"Removed Top Rows" = Table.Skip(#"Expanded Data",15),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Top Rows",{"Column1", "Column2", "Column3", "Column4", "Column9", "Column10"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Column5] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Stock Code 1", type text}, {"Issue Date", type any}, {"Req. No.", type text}, {"Unit", type text}, {"Qty.", type any}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Stock Code 1", "Stock Code"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Issue Date] <> "Issue Date")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Issue Date", type date}, {"Qty.", Int64.Type}})
in
    #"Changed Type1"[/SIZE]

then filter by month

 

[mumps]

How do you determine the search month? Do you base the search on the month in cell A3 of each Stock Sheet? Will the month in all the Stock sheets always be the same?

 

[arijitirf]

Due to strict IT Rules in my office I cannot use Power Query. It will be very helpful to me if you can make this in excel vba. Thanks for your prompt reply.

 

[sandy666]

Sorry but I am not interested in vba in any form.
Maybe someone else
Good luck and have a nice day

 

[arijitirf]

Search Month will be determined by the dates in E7:E. I require monthwise data and those dates will fall in search month will display in the Master Sheet.

 

[mumps]

Currently in the master sheet you have data only for the month of January (month 1). Cell A3 in each Stock sheet also has the month of January but each Stock sheet also contains data for the month of February (month 2). It seems that the Master sheet is pulling data only for the month in cell A3 of each Stock sheet. Is this correct?

 

[arijitirf]

Data will be pulled from E4:E based on the month search. For example, if I need to pull data for the month of January-2018 then the code will only pull the dates entered in E4:E, which fall in January-2018. Again, if I need to pull data for the month of February-2018 then the code will do the same thing.

Thanks for your prompt reply and taking interest on my topic.

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, bottomB As Long, ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Master")
    Dim response1 As Long, response2 As Long
    response1 = InputBox("Please enter the month number (1-12).")
    response2 = InputBox("Please enter the year.")
    For Each ws In Sheets
        If ws.Name <> "Master" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("E2:E" & LastRow).AutoFilter Field:=1, Criteria1:=">=" & DateSerial(response2, response1, 1), Criteria2:="<=" & DateSerial(response2, response1 + 1, 0)
            Intersect(ws.Rows("4:" & LastRow), ws.Range("E:E,F:F,H:H").SpecialCells(xlCellTypeVisible)).Copy desWS.Cells(desWS.Rows.Count, "C").End(xlUp).Offset(1, 0)
            LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            bottomB = desWS.Range("B" & desWS.Rows.Count).End(xlUp).Row + 1
            desWS.Range("B" & bottomB & ":B" & LastRow) = ws.Name
            ws.Range("E2").AutoFilter
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[arijitirf]

It gives run time error 1004 "We can't do that to a merge cell"