Agfdhyk

I'm new to the this site and new to VBA in general, i'm looking for any help possible? if possible!?

I am trying to copy cell data from one sheet to another based on a reference match.

Basically I want to search for a row in a closed workbook using a reference number, then copy certain cells from that row (not entire row) in certain cells in my open workbook..

I assume a click button would be the best method for this?

so insert reference into open workbook, click button and pull data from closed workbook.. (sounds so easy on paper! ha)

for example.

search ref No.- 123456

Search this ref no. for a particular row within closed workbook "Weekly stats" then copy only cells - D2, E2, F2, G2 & S2 from that particular row into currently opened workbook.

is this possible? would save me so much time!

any help appreciated!

Thanks,

Paste this in your sheet code, adjust input cell & path.
Test by changing value in cell A1.
Some alternatives included for experimenting.

    Private Sub Worksheet_Change(ByVal Target As Range)

                                    'Input cell

    If Not Intersect(Target, Range("A1")) Is Nothing Then

        If Target.Value <> "" Then

            Application.ScreenUpdating = False



            'Row selection by target value

            rwnum = Target.Value



            Dim wb As Workbook



            ''For user selected workbook

            'Dim dP As String

            'dP = Environ$("USERPROFILE") & "" & "Downloads": ChDrive "C:"

            'ChDir dP

            'Dim fNP As Variant

            'fNP = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select file")

            'Set wb = Workbooks.Open(fNP)





            ''For static workbook with static path

            'Set wb = Workbooks.Open("C:UsersUserNameDownloadsWeekly stats.xls")





            ''For static workbook with semi dynamical path

            Set wb = Workbooks.Open(Environ$("USERPROFILE") & "" & "Downloads" & "Weekly stats.xls")

                                    'C:UsersUserName              Downloads     Weekly stats.xls





            Dim rng As Range

            Set rng = wb.ActiveSheet.Range("A1").CurrentRegion



            ''Static columns

            c1 = 1:     c2 = 2:    c3 = 3:    c4 = 4:    c5 = 16



            ''Dynamic columns

            'With rng

            '    c1 = WorksheetFunction.Match("Example header text 1", .Rows(1), 0)

            '    c2 = WorksheetFunction.Match("Example header text 2", .Rows(1), 0)

            '    c3 = WorksheetFunction.Match("Example header text 3", .Rows(1), 0)

            '    c4 = WorksheetFunction.Match("Example header text 4", .Rows(1), 0)

            '    c5 = WorksheetFunction.Match("Example header text 5", .Rows(1), 0)

            'End With



            Dim Mrng As Range



            ''Static destination

            'Set Mrng = Me.Range("D2:S2")



            ''Same row destination

            'Set Mrng = Me.Cells(rwnum, 4).Resize(, 16)



            ''Next destination

            Set Mrng = Me.Range("D1048576").End(xlUp).Offset(1).Resize(, 16)







            With Mrng

                .Cells(c1).Value = rng(rwnum, c1)

                .Cells(c2).Value = rng(rwnum, c2)

                .Cells(c3).Value = rng(rwnum, c3)

                .Cells(c4).Value = rng(rwnum, c4)

                .Cells(c5).Value = rng(rwnum, c5)

            End With



            wb.Close savechanges:=False



            Target.Select



            Application.ScreenUpdating = True

        End If

    End If

End Sub