troubleshooting Question

VBA - Transfer Data from one sheet to another

Avatar of Wilder1626
Wilder1626Flag for Canada asked on
VBAMicrosoft ExcelMicrosoft Office
16 Comments1 Solution12 ViewsLast Modified:

I have an Excel file with 2 sheets in it.
"Template" and "Header".

Starting at row 7 of sheets "Template", i would like to transfer single value from column D to the other sheets.

The value from column D would go in column A and B on sheets "Header" and the value from Column E from sheet "Template" would go in column G on sheets "Header".

sheets "Template"

Result on Sheets "Header"

The problem i have is that i cannot move the value from Column E from sheet "Template" to the column G on sheets "Header".

Private Sub CommandButton1_Click()
   Dim dict As Object, rCell As Range
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    With Sheets("Template")
        For Each rCell In .Range("D7:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            dict.Item(rCell.Value) = Empty
        Next rCell
    End With

    For Each V In dict.Keys()
        With Sheets("Header")
            .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = V
            .Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = V
            .Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = "Test"

            .Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Value = Sheets("Template").Range("E" & .Cells(Rows))
        End With
    Next V
End Sub
How can i do that?

Thanks for your help.

Macro Transfer single value to other sheet.xlsm
Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros