VBA for button to paste data to another worksheet and select next empty row

[Nikijune]

Hello,

I have a worksheet (Input) where users will update information. I then want this info to be copied and pasted into another worksheet (Output) at the end of the day, by click of a button. I have found the below code which copies the first row and pastes it into the first row of the Output worksheet. What can I do to then get this to loop the next 16 rows in the Input worksheet and paste them into the output and by finding the next empty row?

#Private Sub SubmitDailys_Click()

'Copy cells and paste to new worksheet

Sheets("Input").Range("A7, C7, e7, G7, I7, K7, M7, P7, R7:V7").Copy Destination:=Sheets("Output").Range("A2")
End Sub#

 

[dmt32]

Hi,
not tested but see if this does what you want?

Private Sub SubmitDailys_Click()


Sheets("Input").Range("A7:A23, C7:C23, e7:e23, G7:G23, I7:I23, K7:K23, M7:M23, P7:P23, R7:V23").Copy Destination:=Sheets("Output").Range("A" & Rows.Count).End(xlUp).Offset(1)


End Sub

Adjust as required.

Dave

 

[Nikijune]

Thanks Dave - this works a treat.... I'm going to have to start paying you

I do have one query with this though. You have grouped the columns together, however, I only want to transfer the data if the row is populated. So I wouldn't want to transfer all columns, all of the time.
I have re-created the formula above but for each row, but I wanted to check if there was an easier way of doing this as I have a lot of rows that I need to do it for and the rows may increase or decrease month on month?

To give a bit of context, I have created a spread sheet that details daily reports, weekly reports, monthly reports and adhoc reports. Once the report has been completed the user will go in and put their name, the time they started it and the time they finished it and they will click submit to populate the details into the output spread sheet. This is to build a log of who completed what reports, how long it takes and then we can monitor whether we are completing the reports on time. So I have a submit button for daily reports, a submit button for weekly's, a button for monthly's and so on.

Also, can you help with code to only transfer the data if cell B7 is populated?


Thank you kindly

 

[dmt32]

Hi,
my reward is getting the solution right!

Suggestion was, based on what you posted, just a guess as without seeing copy of your workbook with sample data, difficult sometimes to fully picture what it is trying to do.
If able to let me see copy will have a look & see if can help you with your project.

One way to test for an empty cell:

If Len(Range("B7").Value) > 0 Then

Dave