Copy cell values from non contiguous cells in sheet2 in an empty contiguous row in sheet2

[Hardhat]

I have been asked to build a spread sheet to collect data. Sheet1 " collection form" this is where the use enters the data they are collecting.
Sheet2 "Report" is where the data needs to be stored after collecting.

Goal is to take the relevant data from non contiguous cells in sheet1 "collection form" and place them all in the first empty row in sheet2 Report.
and clear the data from the collection sheet so it can be re-entered for the next row in sheet2. Preferably using a submit button.

Example. Click submit button and

Cells B2, B3, K2, A12, A19, A26, A33, A40, B7:B11, B14:B18, B21:B25, B28:B32, B35:30, from work sheet1 "data colletion" Copy to
A2:AF2

In ranges B7:B11, B14:B18, B21:B25, B28:B32, B35:30, there may be empty cells I would not want them copied to sheet2

Each of the cells have a calculation formula that I do not want to copy to sheet2

Once all is copied I would like to have all the cell be cleared of values but not formulas.

Then they can re-enter the information hit the submit button again and have it repeat the operation and place the new information on the next empty row in sheet2.

I am new to VBA I have been able to get the the cells to copy into a new row but I have had to right a separate code for each cell and when there is empty cell above the row is puts the information for that cell above the rest of the information being place in that row.

Thank you for taking the time to read my long explanation

 

[Joe4]

Welcome to the Board!

Here is some code which should get you started. It is just a small part of your range, but it is easy to build upon it. Just getting adding your additional ranges to the range variable.
It will copy the values from the ranges on your "Collection Form" sheet and paste the values to the first available row, starting in column A.

Sub MyCopyMacro()

    Dim dataWS As Worksheet
    Dim repWS As Worksheet
    Dim srcRng As Range
    Dim cell As Range
    Dim nxtRow As Long
    Dim myCol As Long
    
    Application.ScreenUpdating = False
    
'   Set sheet names
    Set dataWS = Sheets("Collection Form")
    Set repWS = Sheets("Report")
    
'   Set source range to copy from
    Set srcRng = dataWS.Range("B2, B3,K2,A12,B7:B9")
    
'   Find next blank row in column A on Report sheet
    nxtRow = repWS.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Loop through all cells populating Report sheet
    myCol = 1
    For Each cell In srcRng
        repWS.Cells(nxtRow, myCol).Value = cell.Value
        myCol = myCol + 1
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[Hardhat]

Wow thanks for the quick response!! I will get working on it and get back to you on how it is working!!

 

[Joe4]

You are welcome.

There are some parts you will need to add, like the clearing of whatever cells need to be cleared afterwards. I wasn't sure about that part, as you mentioned that the cells being copied from contained formulas, not hard-coded values. But the code I posted should be most of the "heavy lifting" needed (I hope).

 

[Hardhat]

It works like a charm. I spent almost 2 days trying to work that out. One question on you response about the clearing process. I have excel formulas in most of the cells, would it be easy to clear values in those cells if I coded the functions? They are just simple countif and sum function.

 

[Joe4]

Wouldn't you want to actually clear the values in the cells that feed those formulas (and not remove the formulas themselves)?

 

[Hardhat]

Correct I would like to keep the formulas in place. But instead of using a formula in the cell I could right a macro to do the same calculations if that would make the coding easier to clear the cells once they have been move to the report form. Just wondering which way would be easier to code the clearing of the Cells and keep them doing their Jobs?

 

[Joe4]

But instead of using a formula in the cell I could right a macro to do the same calculations if that would make the coding easier to clear the cells once they have been move to the report form.

Maybe I am not understanding you fully, but I don't see the advantage.
What is the need to clear the cells with the formulas (especially since you are not entering any data into those cells, I am not sure what you are trying to accomplish by that - it seems to me that you would want to clear the cells you are entering data into).

 

[Hardhat]

you are correct I am over thinking the process.