Xtykutl

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 eg Sheet1(Sheet1), adjust input cell & path.
Test by changing value in cell A1 if unchanged.
Some alternatives included for experimenting, add and remove tick(s)(') to modify.
Double ticks(' ') on explanatory rows(not code) Do not mess with these..
When your preferred result is achieved, remove the unused alternatives(green)

    Private Sub Worksheet_Change(ByVal Target As Range)

                                    ''-Select input cell here

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

        If Target.Value <> "" Then

            ''-Turns off visual updating

            Application.ScreenUpdating = False



            ''-Returns value applied in input cell later used as row reference.

            rwnum = Target.Value



            Dim wb As Workbook



            ''Alt 1'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)





            ''Alt 2'For static workbook name with static path

                                    '               Your path here               '

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





            ''Alt 3'For static workbook name 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



            ''Alt 1'Static columns, select yours! 1=column A, 2=column B and so on..

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



            ''Alt 2'Dynamic columns, if entire columns would move for some reason

            '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

            ''-Where content is "pasted" 

            ''Alt 1'Static destination

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



            ''Alt 2'Same row as in closed workbook and columns 4 to 19(D:S)

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



            ''Alt 3'Next empty row in columns 4 to 19(D:S)

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





            ''-Adds the value to earlier selected columns in above selected range

            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



            ''-Closes the "closed workbook" without saving any changes

            wb.Close savechanges:=False



            ''-Returns selection to input cell for further testing 

            Target.Select



            ''-Turns on visual updating                

            Application.ScreenUpdating = True

        End If

    End If

End Sub