Copy data then remove unused rows

[pingwin77]

I am working on a workbook for my company and need a little help with some coding. There is some data that will be imported into "Insert Data 1" tab. I need that data copied into the "Insert Data 2" tab starting in cell A1.

From there the dta will be populated in the first tab "Expense Report." After the data has been populated, I need the blanks row deleted, starting with row 63 and moving up until al of the blank rows have been removed. The search for the blank cell can be done on column F "total amount."

Can this be done with the existing formulas in the "expense Report" tab or would they need to be changed to an =IF( , ) style fomula to provide you a blank cell to count?

I would also like all of this coding to be executed from a button on the top of the "expense report" tab.

[pingwin77]

I can get the coding to do the copy/paste data part but the searching for and deleting of empty rows is new to me. Can anyone help?

[arlu1201]

If you use this code, you do not have to link the sheets using formulae. It will automatically clear the contents of the Expense report and populate the data. This way, you do not have to delete the $0 cells. (If you still need to delete the blanks, let me know.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[pingwin77]

So I gave the code you suggested a try and it does get the data into the correct location on the correct sheet, however it remove the formatting that is already setup. Is there a way to get it to put just the context into the cells and not the formatting? I have the formatting set up so it works for my accounting department.

I am also wanting the code to remove any of the unused rows at the bottom of the first sheet so that the signature lines move up and the page looks nicer without an blank rows in it. Can this be done?

I added 3 files to show what I am looking for.

Original = The main template
First code = What the first code does
Final = What I want the page to look like once the code is executed.

Thanks in advance for any help with this. My boss is hounding me about this project so I truly appreciate the assistance!