Pfthb

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 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