Copying Variables In Selections

I am working on a macro, which should cycle through a column and find all rows with a given value. Then for these rows it should paste values from other columns into another worksheet.

Example:

1 2 3 4 5 6

Find all cells in row 6 with "yes" and paste the value from row 3 into another workbook.

Issue:

I cant seem to get the the value from row 3.

ActiveWorkbook.Worksheets(datasheet).Cells(rowtocopy, 6).Value.Copy has an error. Currently "Object required". Ive tried: .cells().value.copy that gives me a different error.

Script is started in the workbook where the extracted values should go.

Below its called "Template (2)".

The workbook with the rows is called "Datasheet" (which may be "Volume" or "SNiC data" depending on what is written in Worksheets("Template (2)").Range("B4").Value

Code:

Sub copychanges()
'################ EXTRACT ORIGINAL PAYMENT DATA

originaldata:

Dim currentwb As String, SuppXls As Excel.Workbook, datasheet As String, i As Long, targetcolumn As Range, modcorrect As String, fromcolumn As Range, rowtocopy As String
currentwb = ActiveWorkbook.Name
SuppPath = ActiveWorkbook.path & "" 'file path (assuming all file are in the same folder
SuppName = Worksheets("Template (2)").Range("D4").Value 'filename of corrected file

[Code]....