Need help- macro to scan all sheets for cells in date range, then copy rows to new sheet

I’m new to VBA and this site and while I’ve found code on macros similar to this, I’ve been unable to customize them for my specific needs or cobble several together and have them work with Excel 2013. (I hate to ask for help, but truth be told, I’ve a LOT of learning materials to work through before I can master the current task -- outlined below --on my own!)
My need: Automate the monthly process of determining what corporate forms need to be updated based on date.
My source file: I’ve a workbook composed of 16 worksheets. Each sheet contains info about all the forms used by a different department (ie., there’s a sheet listing QC’s forms, another for IT’s forms). Each sheet lists the forms by row. There are anywhere from 4-16 columns in each sheet, but I am only interested in columns A through G (1-7) for this project; column G will always be the one with the needed date.
My Goal: a button click will prompt users to enter a beginning and end date. Then the macro would scan column G in EVERY sheet in the workbook to find dates falling in that range. Whenever found, the macro would copy columns A through G (1-7) in the row and paste them into an new sheet called “Report”, starting on row 2, columns B-H (2-8). We can assume all other worksheets are numbered in consecutive order, Sheet1, Sheet2, etc.
An example:
macro-help.jpg
I'd thought about including details about what I'd already tried, but at this point my head is spinning and I think I need to start fresh.
So, until I get “up to speed” with Excel VBA macros, I’d appreciate any help I can get with this.
Thanks!
BrenFred

Since you did not provide a worksheet to test this code with, this is an untested code.

Advise as necessary

Please Login or Register  to view this content.

If this does not work for you, then provide a sample workbook uploaded for analysis.

Revised code

Please Login or Register  to view this content.

Create a new sheet called Report and then run the code.