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

wkdintent

New Member
Joined
Jan 5, 2018
Messages
8
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,230
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jan 5, 2018
Messages
8
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,230
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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
 
Last edited:

wkdintent

New Member
Joined
Jan 5, 2018
Messages
8

ADVERTISEMENT

Ah you beauty!

Thankyou very much.

Jim
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,220
Office Version
  1. 365
Platform
  1. Windows
Hello wkdintent,

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

Code:
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.
 
Last edited:

wkdintent

New Member
Joined
Jan 5, 2018
Messages
8

ADVERTISEMENT

Thanks. I might look into that for version 2.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,230
Office Version
  1. 2013
Platform
  1. Windows
I could have done the same like this:

Code:
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top