Copy the data in any active row in Sheet 1 to specific cells in Sheet 6

[cti4sw]

As a failure analysis engineer, I keep a log of all cases I work on. Something I'd like to do with this data is automatically populate a check sheet on Sheet #6 based on the row I select in the case log on Sheet #1 when the "Populate Fields" button on Sheet #6 is clicked.

I've uploaded the workbook itself so you can see what I'm looking for; this is not all I'd like to do with this, but I need to learn as I go, so it's a good start. Unfortunately I don't have any hardcopy reference material to work with as yet.

Thanks to anyone who can help!


P.S. Instead of re-uploading the finished product, as some of you sometimes are generous enough to do, I'd like to see the code and try to apply it myself. Thanks!

[xladept]

Here's some event code, but you may want to not use the automatic trigger??

'automatically populate a check sheet on Sheet #6 based on the row
'I select in the case log on Sheet #1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call JSB(Target)
End Sub
Sub JSB(Target As Range): Dim wg As Worksheet, wc As Worksheet, r As Long
Set wg = ActiveWorkbook.Worksheets("JSB_FA_Log")
Set wc = ActiveWorkbook.Worksheets("Check_Sheet"): r = 1
Do Until wc.Range("B" & r) = "Notes:": r = r + 1: Loop: r = r + 3
With wc
Cells(r, "B") = "RMA:": Cells(r, "D") = wg.Cells(Target.row, "B")
Cells(r, "F") = "Case:": Cells(r, "H") = wg.Cells(Target.row, "A")
Cells(r, "J") = "Failure Analysis Findings:": r = r + 1

Cells(r, "B") = "Serial #:": Cells(r, "C") = wg.Cells(Target.row, "C")
Cells(r, "F") = "Date:": Cells(r, "G") = wg.Cells(Target.row, "K"): r = r + 1

Cells(r, "B") = wg.Cells(Target.row, "C"): Cells(r, "F") = "Bin:": Cells(r, "H") = "Flow:"
Cells(r, "G") = wg.Cells(Target.row, "E") & wg.Cells(Target.row, "D"): r = r + 1

Cells(r, "B") = "Dump:": Cells(r, "C") = "X": Cells(r, "D") = "DCP:"
Cells(r, "F") = "Orientation:": r = r + 1

Cells(r, "B") = "Stated Failure Mode:": Cells(r, "C") = "Flow Control"
Cells(r, "F") = "HLD:": Cells(r, "H") = "HLU:": r = r + 1

Cells(r, "F") = "VID:": Cells(r, "H") = "VIU:": r = r + 1

Cells(r, "F") = "HBD:": Cells(r, "G") = "X": Cells(r, "H") = "HBU:": r = r + 1

Cells(r, "B") = "Internal Leak (sccm):": Cells(r, "D") = "10 psig:"
Cells(r, "F") = "30 psig:": Cells(r, "H") = "60 psig:": r = r + 1

Cells(r, "B") = "Cal Date:": Cells(r, "D") = "?"
Cells(r, "F") = "As-Rec'd Zero:": Cells(r, "H") = "?": r = r + 1

Cells(r, "B") = "Power ON (hours):": Cells(r, "F") = "Gas ON:": r = r + 1

Cells(r, "B") = "Warranty:": Cells(r, "C") = "X": Cells(r, "D") = "Billable:"
Cells(r, "F") = "Repair:": Cells(r, "G") = "X": Cells(r, "H") = "Scrap:": r = r + 1

Cells(r, "B") = "Notes:": Cells(r, "J") = "Service Disposition:": r = r + 1

Cells(r, "B") = "This form was created by Jonathan Ball, Failure Analysis Dept." & _
"(2013) and is not an official Brooks Instrument form."

End With
End Sub
Sub TestJSB(): Dim wg As Worksheet, wc As Worksheet, r As Long, T As Range
Set wg = ActiveWorkbook.Worksheets("JSB_FA_Log")
Set wc = ActiveWorkbook.Worksheets("Check_Sheet"): r = 1
Set T = wg.Range("A12")
Call JSB(T)
End Sub

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select The sheet from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[cti4sw]

Yeah, you're right, I don't want an automatic trigger. As you saw in my workbook, I have a pre-made button on the Check Sheet that says "Populate Fields" that when clicked, would activate this macro based on the row selected in the FA Log. Is there any way you could show me how to tie the code to that button? Is it as simple as having the code say this:

Sub Populate_Fields()

Call Option Explicit

End Sub

Thanks

EDIT: Just pasted & tested this. It didn't do anything correctly... I wanted the information in the active row from each column in the FA Log to display in its corresponding space on the Check Sheet ("RMA" from the active row in the FA Log to display in the cell adjacent to the "RMA" label in the Check Sheet, and so on), but this macro pasted the labels themselves into the Notes and Service Disposition sections of the Check Sheet.

[xladept]

Sub JSB(Target As Range): Dim wg As Worksheet, wc As Worksheet, r As Long
Set wg = ActiveWorkbook.Worksheets("JSB_FA_Log")
Set wc = ActiveWorkbook.Worksheets("Check_Sheet")
r = wc.Range("B" & wc.Rows.Count).End(xlUp).row + 2
With wc
.Cells(r, "B") = "RMA:": .Cells(r, "D") = wg.Cells(Target.row, "B")
.Cells(r, "F") = "Case:": .Cells(r, "H") = wg.Cells(Target.row, "A")
.Cells(r, "J") = "Failure Analysis Findings:": r = r + 1

.Cells(r, "B") = "Serial #:": .Cells(r, "C") = wg.Cells(Target.row, "C")
.Cells(r, "F") = "Date:": .Cells(r, "G") = wg.Cells(Target.row, "K"): r = r + 1

.Cells(r, "B") = wg.Cells(Target.row, "C"): .Cells(r, "F") = "Bin:": .Cells(r, "H") = "Flow:"
.Cells(r, "G") = wg.Cells(Target.row, "E") & wg.Cells(Target.row, "D"): r = r + 1

.Cells(r, "B") = "Dump:": .Cells(r, "C") = "X": .Cells(r, "D") = "DCP:"
.Cells(r, "F") = "Orientation:": r = r + 1

.Cells(r, "B") = "Stated Failure Mode:": .Cells(r, "C") = "Flow Control"
.Cells(r, "F") = "HLD:": .Cells(r, "H") = "HLU:": r = r + 1

.Cells(r, "F") = "VID:": .Cells(r, "H") = "VIU:": r = r + 1

.Cells(r, "F") = "HBD:": .Cells(r, "G") = "X": .Cells(r, "H") = "HBU:": r = r + 1

.Cells(r, "B") = "Internal Leak (sccm):": .Cells(r, "D") = "10 psig:"
.Cells(r, "F") = "30 psig:": .Cells(r, "H") = "60 psig:": r = r + 1

.Cells(r, "B") = "Cal Date:": .Cells(r, "D") = "?"
.Cells(r, "F") = "As-Rec'd Zero:": .Cells(r, "H") = "?": r = r + 1

.Cells(r, "B") = "Power ON (hours):": .Cells(r, "F") = "Gas ON:": r = r + 1

.Cells(r, "B") = "Warranty:": .Cells(r, "C") = "X": .Cells(r, "D") = "Billable:"
.Cells(r, "F") = "Repair:": .Cells(r, "G") = "X": .Cells(r, "H") = "Scrap:": r = r + 1

.Cells(r, "B") = "Notes:": .Cells(r, "J") = "Service Disposition:": r = r + 1

.Cells(r, "B") = "This form was created by Jonathan Ball, Failure Analysis Dept." & _
"(2013) and is not an official Brooks Instrument form."

End With
End Sub


Sub Populate_Fields(): Dim T As Range, S
Set T = Selection
S = T.Address
Call JSB(T)
End Sub

Your Populate_Fields is in red above - but I changed the code a little so recopy and paste the whole thing - what kind of button are you using - your book didn't fully translate to my system??

Did you just want to write the check sheet in the same space?? if so just change the r setting to

r=2

instead of

r = wc.Range("B" & wc.Rows.Count).End(xlUp).row + 2