VBA to copy line from one sheet into specific cells on another sheet?

Carterland

New Member
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

New to the site so thanks for having me, I'm also new to VBA so be gentle lol

Basically I'm trying to speed up a process for something at work which involves trying to creat a macro button that would allow me to highlight a row, press a button and the data from specific cells in that row copied and pasted (values only) into another worksheet in specific cells as I've set up a template.

Firstly, is it possibly? And secondly how lol

The data would need to be as follows

From selected row on sheet names 'Log' copy cells to sheet named 'Feedback Template'

G - C6
L - C8
M - C10
K - C12
Q - C14
R - C17

Where I've specified the column on the above for the copy part, that would need to look in the highlighted tow for the correct number of the line.

If anyone could help I'd be so grateful.

Thanks in advance

Shaun
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
Maybe you can use lines as:
VBA Code:
Worksheets("Feedback Template").Range("C6") = Worksheets("Log").Range("G" & ActiveCell.Row)
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Are you selecting one row at a time and every time the output will be displayed on C6, C8, C10.. cells.

Thanks,
Saurabh
 

Carterland

New Member
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Are you selecting one row at a time and every time the output will be displayed on C6, C8, C10.. cells.

Thanks,
Saurabh
Hi there,

Yes it will always only be one line and the cells it will be pasted into won't change.

Thanks
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,

If you are selecting one row then add a button and attach below macro.

VBA Code:
Sub copySelected()
Dim rng As Range
Set rng = Selection

    With Sheets("Feedback Template")
        Range("C6") = Sheets("Log").Range("G" & rng.Row)
        Range("C8") = Sheets("Log").Range("L" & rng.Row)
        Range("C10") = Sheets("Log").Range("M" & rng.Row)
        Range("C12") = Sheets("Log").Range("K" & rng.Row)
        Range("C14") = Sheets("Log").Range("Q" & rng.Row)
        Range("C17") = Sheets("Log").Range("R" & rng.Row)
    End With

End Sub
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Try
VBA Code:
Sub test()
    With Worksheets("Feedback Template")
        .Range("C6") = Worksheets("Log").Range("G" & Selection.Row)
        .Range("C8") = Worksheets("Log").Range("L" & Selection.Row)
        .Range("C10") = Worksheets("Log").Range("M" & Selection.Row)
        .Range("C12") = Worksheets("Log").Range("K" & Selection.Row)
        .Range("C14") = Worksheets("Log").Range("Q" & Selection.Row)
        .Range("C17") = Worksheets("Log").Range("R" & Selection.Row)
    End With
End Sub
 

Carterland

New Member
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try
VBA Code:
Sub test()
    With Worksheets("Feedback Template")
        .Range("C6") = Worksheets("Log").Range("G" & Selection.Row)
        .Range("C8") = Worksheets("Log").Range("L" & Selection.Row)
        .Range("C10") = Worksheets("Log").Range("M" & Selection.Row)
        .Range("C12") = Worksheets("Log").Range("K" & Selection.Row)
        .Range("C14") = Worksheets("Log").Range("Q" & Selection.Row)
        .Range("C17") = Worksheets("Log").Range("R" & Selection.Row)
    End With
End Sub
This worked perfectly!

Thanks so much, this will save so much time.

Very much appreciated for all that helped.

Have a great day!
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
You are welcome
And thank you for the feedback
Be happy
 

Carterland

New Member
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi
You are welcome
And thank you for the feedback
Be happy
Hi,

Sorry to bother you again, so this works absolutely perfectly if I put the macro button on the 'log' sheet, is there any way of making this work if the button is located on the 'feedback' tab?

I tried adding the button there and it doesn't work, I'm assuming it's not searching in the right place due to the location of the button and the fact the feedback tab is the active sheet in the above scenario. I just think it would be cleaner if I could add the button to the feedback tab so I'd highlight the line on the 'log' tab then click the button on the 'feedback' tab which would pull through the data.

Apologies, I didn't realise this would be much cleaner until I actually used it.

Thanks in advance :)
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Well it need some modification
But I have to go right now
Tomorrow I'll have a look at it
Sorry
 

Watch MrExcel Video

Forum statistics

Threads
1,127,838
Messages
5,627,181
Members
416,227
Latest member
Abid Fattani

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