Pulling Data from multiple sheet

A

arijitirf

Board Regular
Joined
Aug 11, 2016
Messages
118
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]
Stock Code​
[/td][td=bgcolor:#70AD47]
Issue Date​
[/td][td=bgcolor:#70AD47]
Req. No.​
[/td][td=bgcolor:#70AD47]
Unit​
[/td][td=bgcolor:#70AD47]
Qty.​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 1​
[/td][td=bgcolor:#E2EFDA]
11/01/2018​
[/td][td=bgcolor:#E2EFDA]
EL-1​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 1​
[/td][td]
12/01/2018​
[/td][td]
EL-4​
[/td][td]
Ltr.​
[/td][td]
150​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 1​
[/td][td=bgcolor:#E2EFDA]
15/01/2018​
[/td][td=bgcolor:#E2EFDA]
EL-6​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
200​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 1​
[/td][td]
20/01/2018​
[/td][td]
EL-7​
[/td][td]
Ltr.​
[/td][td]
100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 1​
[/td][td=bgcolor:#E2EFDA]
22/01/2018​
[/td][td=bgcolor:#E2EFDA]
EL-9​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
300​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 1​
[/td][td]
31/01/2018​
[/td][td]
EL-11​
[/td][td]
Ltr.​
[/td][td]
250​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 1​
[/td][td=bgcolor:#E2EFDA]
02/02/2018​
[/td][td=bgcolor:#E2EFDA]
EL-12​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 1​
[/td][td]
05/02/2018​
[/td][td]
EL-14​
[/td][td]
Ltr.​
[/td][td]
300​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 1​
[/td][td=bgcolor:#E2EFDA]
07/02/2018​
[/td][td=bgcolor:#E2EFDA]
EL-18​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
250​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 1​
[/td][td]
08/02/2018​
[/td][td]
EL-19​
[/td][td]
Ltr.​
[/td][td]
150​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 1​
[/td][td=bgcolor:#E2EFDA]
09/02/2018​
[/td][td=bgcolor:#E2EFDA]
EL-21​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
250​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 2​
[/td][td]
11/01/2018​
[/td][td]
HVAC-5​
[/td][td]
Ltr.​
[/td][td]
1500​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 2​
[/td][td=bgcolor:#E2EFDA]
15/01/2018​
[/td][td=bgcolor:#E2EFDA]
HVAC-7​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
800​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 2​
[/td][td]
20/01/2018​
[/td][td]
HVAC-9​
[/td][td]
Ltr.​
[/td][td]
100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 2​
[/td][td=bgcolor:#E2EFDA]
05/02/2018​
[/td][td=bgcolor:#E2EFDA]
HVAC-20​
[/td][td=bgcolor:#E2EFDA]
Ltr.​
[/td][td=bgcolor:#E2EFDA]
300​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 2​
[/td][td]
07/02/2018​
[/td][td]
HVAC-23​
[/td][td]
Ltr.​
[/td][td]
250​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 3​
[/td][td=bgcolor:#E2EFDA]
21/01/2018​
[/td][td=bgcolor:#E2EFDA]
PL-11​
[/td][td=bgcolor:#E2EFDA]
No.​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 3​
[/td][td]
03/02/2018​
[/td][td]
PL-13​
[/td][td]
No.​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 3​
[/td][td=bgcolor:#E2EFDA]
05/02/2018​
[/td][td=bgcolor:#E2EFDA]
PL-15​
[/td][td=bgcolor:#E2EFDA]
No.​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 3​
[/td][td]
07/02/2018​
[/td][td]
PL-17​
[/td][td]
No.​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 4​
[/td][td=bgcolor:#E2EFDA]
21/01/2018​
[/td][td=bgcolor:#E2EFDA]
GEN-4​
[/td][td=bgcolor:#E2EFDA]
No.​
[/td][td=bgcolor:#E2EFDA]
80​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 4​
[/td][td]
03/02/2018​
[/td][td]
GEN-9​
[/td][td]
No.​
[/td][td]
150​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 4​
[/td][td=bgcolor:#E2EFDA]
05/02/2018​
[/td][td=bgcolor:#E2EFDA]
GEN-11​
[/td][td=bgcolor:#E2EFDA]
No.​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
Stock Code 4​
[/td][td]
07/02/2018​
[/td][td]
GEN-17​
[/td][td]
No.​
[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
Stock Code 4​
[/td][td=bgcolor:#E2EFDA]
13/02/2018​
[/td][td=bgcolor:#E2EFDA]
GEN-30​
[/td][td=bgcolor:#E2EFDA]
No.​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]
[/table]

try PowerQuery

Code: 
[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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Sorry but I am not interested in vba in any form.
Maybe someone else
Good luck and have a nice day
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Try:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Look up todays date and special copy paste value for multiple sheets
Replies
4
Views
169
OaklandJim
O
E
  • Solved
Copy a certain set of data from one sheet to another depending on the date typed in
2
Replies
10
Views
507
myall_blues
myall_blues
M
  • Question Question
Create Report across sheets based on two dates as in my attachment
Replies
0
Views
141
Maklil
M
R
  • Question Question
Data required in User Form Box based on selection
Replies
2
Views
89
RAMU
R
T
  • Question Question
Pull data from multiple sheets that match varying criteria
Replies
1
Views
666
bobaol
B

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top