Selected Answer
Don't despair, Elaine. Fact is that you did a great job so far. But you didn't arrive at a question that can be asked and answered here. In my response I shall focus, therefore, on how to cut your project into managable parts.
The theory would have you start with an idea for the user interface (UI) and the output (Sheet2) with which the input (Sheet1) is coordinated. Data manipulation comes a distant fourth in sequence, except that the layout of both input and output must facilitate the same. That's the point at which it is easy to lose direction. When that happens, go back to the red line provided by theory.
Your output sheet doesn't seem well coordinated with the input. It seems like you were considering data manipulation in making the design (copy complete rows from Sheet1). That is worng. The output sheet should only contain those data which you want to show. You seem to be planning to copy about 4 times as many data into the output as you want to show. That will not be efficient.
Your output sheet has many hidden columns. Human curiosity leads to wanting to see what's hidden. If you want the data to be viewable on demand you are laying the groundwork here for vast waste of company time. You could easily create many different reports, all extracted from Sheet1, and all working on the same basic formula. Or you could change the UI to allow the user to select many different reports from the same basic template. Instead of allowing the user to search, give them a button to press and make sure they get exactly what they were looking for.
That brings us to the UI. The UI controls the work flow. Please be aware that you are deciding the work flow, meaning the user's efficiency, and that should rank higher in your mind than the convenience of programming. You plan on having a user form on which the user enters start and end dates. But you didn't provide a button from which to call the user form.
Your output sheet shows a "Sheet Date" ("December 2018") which I presume to be extracted from the start & end dates. This date is misleading, especially since the underlying dates aren't shown. If you want to show entries for "December" you don't need start and end dates. If you want start and end dates you should provide for them to be in different months. If you want them to define parts of a month they must be shown.
From the above thoughts a radically different design for the output sheet emerges.
- Start and End date must be shown, unless you want to limit the action to select dates by month only.
- There must be a button to start the process.
- There might be a drop-down from which to select which columns are to be shown (different kidns of reports).
- There should be no hidden columns.
- Using VBA it will take more effort to manage the space above an existing total row than to create such a row on the fly. However, you might consider using a table (ListObject) which would manage the total row for you.
In my opinion, the above requirements point to no user form. The user can enter the start date, end date, in the header of Sheet2, click the button and the report gets done. You might get VBA to format the date entries as the user enters them. There might be a report selector drop-down in addition. If you want to create a table it would cover the rows 3:35 of your design. Rows 1:2 can't be merged and must be re-designed to match the established requirements.
I avoid judging the input sheet (Sheet1). Its job is to be unchangable. It's the database where all information is stored. It seems you are already rather advanced in its design. Changes to it, if they are desirable from the point of view of the output sheet, should be rejected. The database is married to data capture. It must be easy and efficient to enter data into it free of mistakes. Datasets of 130 data are definitely not easy to enter and therefore difficult to create or maintain without errors. I presume that Sheet1 is, in fact, a summary of many databases where data can be captured easily, efficiently, quickly and correctly. That, of course, is one more reason not to change it while talking about a report based on it.
At this point of the planning you now have a data source and a data destination. You also have a UI which is integrated into the output sheet. You will be ready to design a procedure that runs when the button on the report is pressed. Here is the design for it.
Sub CreateReport()
' Find the row of the first date in Sheet1 which is >= StartDate
' Find the row of the last date in Sheet1 which is <= End Date
' Set a range for all the rows between (and including) the two rows
' Assign the value of that range to an array (input array)
' Create an array of all the column numbers you wish to copy (copy array)
' Create an output array equal in size to the output form but with many extra rows
' Loop through all the rows in the input array
' Loop through all the columns in each row
' Transfer the value of the column to the output array if the number is in the copy array
' Redim the putput array to discard unused extra rows
' Adjust the number of rows in the output sheet
' Paste the output array to the output sheet
' Set output sheet headers, if requried
End Sub
Each of the points in the above list requires preparation and execution. Usually the preparation requires the greater effort. There may be questions regarding the preparation or the execution. The great thing is that you will be able to record steady progress as you work your way towards the end of the list. You will be able to avoid getting totally stuck.
Good luck!