Transfer data from form to next sheet in NEXT AVAILABLE COLUMN

Hello all,

I've run into an issue working on a small project. I hope you can help!

The project:

A workbook in which sheet 1 is a form for people to input into 2 adjacent columns time spent on different tasks at the end of each work week. Sheet 2 is identical to sheet 1, but has the intended purpose of storing the data input into the form in sheet 1.

There is a button at the bottom of the form in sheet 1 with an assigned macro that effectively transfers all the data to sheet 2 and then clears sheet 1 for another entry.

The issue:

I cannot get the data to transfer to the next available set of 2 adjacent columns in sheet 2. It keeps repopulating the first 2 columns.

Any help on modifying the macro to transfer data to the next available set of 2 columns on sheet 2 would be much appreciated.

Thanks!

Perhaps somthing like this?

Please Login or Register  to view this content.

Word of warning since you have data validation in columns D and E in sheet "Data" I could not use the "ActiveSheet.UsedRange.Columns.Count" command as this counted the empty columns as well.

So I've use the "CountA" funtion on row 2 to find the traget column when copying so there must always be a value in both D2 and E2 in sheet "Form_R" to ensure that the next pasting is placed in the proper column.

Alf

Thanks, Alf!

Data validation in columns D and E of sheet "Data" was an erroneous product of testing the macro as I had it originally recorded -- it would copy over the data validation settings from sheet "Form_R".

It is unlikely that there will always be a value in both D2 and E2 of "Form_R", so I have removed data validation. Could you explain briefly how I can use the "ActiveSheet.UsedRange.Columns.Count" command now that it is an option?

Thanks!

Sam

I had a bit of rethink and perhaps this macro wil suit you better?

Please Login or Register  to view this content.

Since macro uses the command "PasteSpecial xlPasteAll" this will copy the data validation formate from sheet "Form_R" to "Data" sheet so just leave this formate on "Data" sheet as the macro now takes this in account.

After activating sheet "Data" macro first finds number of columns used i.e. for an "empty" "Data" sheet this number will be 5 on account of the data validation formatein column D and E but even if there are values in coumn D and E this value will still be 5 so I've added an extra check by testing the sum of range "D2:E50". If this is 0 then we know that the range D2:E50 is really empty and result should be copied to range D2 in sheet "Data".

If on the other hand the sum of range "D2:E50" is not 0 i.e. not empty then data from sheet "Form_R" should be copied to Cells(2, i + 1) i.e the cell in row 2 and column 6 as both conditions must be fullfilled (i = 5 and SUM = 0) if result should be copied to range D2 in sheet "Data"

Alf

Alf,

Thank you so much! Unfortunately, I just solved my own problem.

I've been working away at this all day (I've never coded before and this is really fun!). I came up with a solution by coming at it from a different angle... and reading, failing, reading some more, failing some more...

I was able to piece together this:

Please Login or Register  to view this content.

MED T&E_TEMPLATE_restructure.xlsm

I abandoned the vertically-oriented sheet "Data" for a horizontally-oriented sheet "Data_R". By using NextRow and Transpose I was able to get everything to transfer over. The Data Validation still comes along with the data, but this won't be an issue as this sheet will be locked to users of the form.

Thank you and everyone else again for all your help! Like I said, I've never done this before and I had a lot of fun working it out! I think I'll be stopping through here more often

Sam

Unfortunately, I just solved my own problem

Why unfortunately? What you did is always the best ways to solve a problem because then you know what you are doing and if things go wrong it's much easier for you to fix your problem.

So welcome back to the forum!

Alf

Ps I think your new sheet layout better.