Search for row in closed workbook, then copy Specific data cells from a row (Not the entire row) into my current workbook

Question

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,

Welcome to Stack Overflow. Before you ask a question, there is an expectation that you show some effort to solve a specific problem. Stack Overflow is not a free code writing or resource discovery service. — Dragonthoughts

mrkrister mrkrister · Accepted Answer · 2018-11-08T14:28:26

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:\Users\Drew\Downloads\Weekly stats.xls")


            ''Alt 3'For static workbook name with semi dynamical path
            'Set wb = Workbooks.Open(Environ$("USERPROFILE") & "\" & "Downloads\" & "Weekly stats.xls")
                                    'C:\Users\UserName         \     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 row
            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

Search for row in closed workbook, then copy Specific data cells from a row (Not the entire row) into my current workbook

1 Answers