VBA - Copy data from one sheet to another, pasting in a row specified in cell

[wkdintent]

Hi all

I have looked through as many of these posts as I can find and couldn't see one which covers it.

I have two sheets "DB" and "Experiment 10". In the future i'll add "Experiment 20", "Experiment 30", "Experiment 40" etc., but for now I'll stick to these two.

Sheet "Experiment 10" is used for data entry; the user will be presented with a few questions, and they type in the numerical or text answers in a designated cell. It will also have the number 10, written in cell B5.

When the user has answered the questions, they will click a button (activating a macro) which will copy the answers to Row 10 of sheet "DB".

Here's where I have been struggling. When I come to create "Experiment 20", I want to be able to duplicate "Experiment 10", and simply change the number in cell B5 to 20; no alterations to the macro. This would change the sheet's identity; so when the user completes Experiment 20's questions, the same macro is run, but it would copy the info to row 20 of sheet "DB".

Eventually there will be lots of Experiment sheets, and I want to be able to duplicate any Experiment sheet, stick in it's number in cell B5, and it'll copy the data to the correct row.

Thanks.

 

[My Aswer Is This]

You said:
"When the user has answered the questions, they will click a button (activating a macro) which will copy the answers to Row 10 of sheet "DB"."

Copy the answers?

Where are the answers?
Are the answers on Row(5)?

 

[wkdintent]

Sheet "Experiment 10" is really basic to look at. It has the number 10 in B5; it then has the first question in D13; and it's answer would be typed in cell E13. The second question is in J14 and it's answer would go in K14. The questions aren't on the same line or row because when I screenshot this sheet, it needs to visually look like our current paper system.


So the macro would copy the contents of E13 to Sheet "DB" G10; and the contents of K14, would go to Sheet "DB" L10.

Essentially, I was looking for something like: Range("E13").Copy Destination:=Sheets("DB").Range("G10")
But with the '10' of G10 being read from "Experiment 10" B5.

This excel file might sound all over the place, but I am trying to simplify my description to save reading and writing time.

 

[My Aswer Is This]

I think this should get you started.
Run this script from the sheet with the questions which in this case is sheet named "Experiment 10"

But the script cares not about the sheet name. So this script will work on any sheet.
But it does copy the answers back to sheet named "DB" in all cases.
It always looks in range("B5") for the proper row.
You should see how it works and finish the other answers.

Sub Copy_Ansers()
'Modified 1-5-2018 5:45 AM EST
Dim ans As Long
ans = ActiveSheet.Range("B5").Value
ActiveSheet.Range("E13").Copy Sheets("DB").Cells(ans, "G")
ActiveSheet.Range("K14").Copy Sheets("DB").Cells(ans, "L")
End Sub

 

[wkdintent]

Ah you beauty!

Thankyou very much.

Jim

 

[vcoolio]

Hello wkdintent,

FWIW, here's another option which bypasses the clipboard:-

Sub TransferData()

        Dim RNum As String
        Dim sh As Worksheet
        
RNum = ActiveSheet.[B5].Value
If RNum = vbNullString Then Exit Sub
Set sh = Sheet2 '----> Change to suit

        sh.Range("G" & RNum) = ActiveSheet.[E13]
        sh.Range("L" & RNum) = ActiveSheet.[K14]

End Sub

Cheerio,
vcoolio.

 

[wkdintent]

Thanks. I might look into that for version 2.

 

[vcoolio]

 

[My Aswer Is This]

I could have done the same like this:

Sub Copy_Ansers()
'Modified 1-5-2018 6:55 AM EST
Dim ans As Long
ans = ActiveSheet.Range("B5").Value
Sheets("DB").Cells(ans, "G").Value = ActiveSheet.Range("E13").Value
Sheets("DB").Cells(ans, "L").Value = ActiveSheet.Range("K14").Value
End Sub