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.

 

[arijitirf]

Sometime it says object variable or with block variable not set

 

[mumps]

I would start by unmerging the merged cells. You should avoid merging cells at all cost because they almost always create problems for Excel macros. When you get the error, ehich line of code is highlighted when you click "Debug"? Without seeing your workbook, it's hard to determine the problem. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[arijitirf]

Sir please find the link below of my excel workbook.

https://www.dropbox.com/s/klh8pol7h5...ster.xlsx?dl=0

I've tried running your code after un-merging the merged cell and stuck with the errror of "Object Variable or With Block Variable not set."

Thank you so much for patronizing my post.

 

[mumps]

I tried the macro on the file you uploaded and it worked without any error. Click here to download your file.

 

[arijitirf]

My God!!! Understood why your code is not working in my sheet.... In my workbook, the sheet namely Master had merged cell...

Thank you so much Sir.. You are really genius...

Warm Regards

 

[mumps]

You are very welcome and thank you for the kind words.

 

[arijitirf]

Facing a weird problem... when the date search not found any dates in Stock Code 1 as per response1 and response2, it pulls sheet name wrongly.. Help Required.

 

[mumps]

Please upload a copy of the file that is giving you problems and explain in detail what the problem is.

 

[arijitirf]

https://www.dropbox.com/s/8lwsk343174n3sg/Stock Register.xlsx?dl=0

In Stock Code 1 there is no dates that fall between 1st to 31st of January' 2018 and if I want pull data against January' 2018 then inspite of having no data in Stock Code 1 it is pulling the sheet name "Stock Code 1" in "Master" (I add a line "On Error Resume Next" in between Line No. 8 & 9 when I was getting a error "Object Variable or With Block Variable not Set.")

Thanks in advance.

 

[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)
            If ws.Cells(Rows.Count, "E").End(xlUp).Row > 2 Then
                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
            End If
            ws.Range("E2").AutoFilter
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub