data indexing macro

[ad9051]

Hello first up many thanks for taking the time to look at my problem! The problem essentially invloves offsetins some data and replacing one of the values based on indexing a name in a one list, and locating it in the next :S Very hard to explain! Please see example much clearer!

Many thanks

Alan

[JBeaucaire]

Something like this:

Option Explicit

Sub CopyValuesTo10Table()
Dim wsM  As Worksheet
Dim MyV  As Double
Dim Rw   As Long
Dim FR   As Long
Dim LR   As Long
Dim rFnd As Range

Set wsM = Sheets("Master sheet")
On Error Resume Next

With Sheets("Data input")
    .Range("D:E").MergeCells = False
    FR = .Range("D:D").Find("Name", LookIn:=xlValues, LookAt:=xlWhole).Row
    LR = .Range("D" & .Rows.Count).End(xlUp).Row
    
    For Rw = FR To LR
        MyV = .Range("E" & Rw)
        With .Range("D" & Rw)
            If .Value <> "Name" And .Value <> "" And MyV > 0 Then
                Set rFnd = wsM.Range("A:A").Find(.Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not rFnd Is Nothing Then
                    rFnd.Offset(, 1).Resize(, 9).Value = rFnd.Offset(, 2).Resize(, 9).Value
                    rFnd.Offset(, 10).Value = MyV
                    Set rFnd = Nothing
                End If
            End If
        End With
    Next Rw
End With

End Sub

[ad9051]

Thats brilliant, i can edit it to make it work for my working example! What changes do i need to make to make reference a sheet in a different workbook?

Your reputation has allredy been ammended by the way!

Many thanks

Alan

[JBeaucaire]

What changes do i need to make to make reference a sheet in a different workbook?

That opens a whole other can of worms.

1) Which of the two sheets referenced in the existing macro is in this "other workbook"?
2) What's the full path and name to this other workbook?
3) Should the macro check to see if the other workbook is even open?
4) If not open, it would need to open it, yes?
5) When the macro is done, it needs to close the other workbook if it was closed originally, yes?

This simple question adds a magnitude issues. Defensive coding....

[ad9051]

Thank you verymuch for your reply, i understand this does add some complexity to the problem.

My answeres are the following:-
1) The "Master Sheet" sheet is the one what will need to be opened

2)

'Directory Folder Location
    ChDir "C:\Data\"
    
    'Workbook / Spreadsheet Location
    Workbooks.Open Filename:= _
        "C:\Data\Full Master Data.xlsm"

3) Yes, if already open continue

4) Yes, if its not open it will need to open

5) It will need to close every time its finished regardless of its previous state.

Many thanks

Alan

[JBeaucaire]

Give this a whirl, i assumes this macro has been inserted into the workbook containing the Data Input sheet:

Option Explicit

Sub CopyValuesTo10Table()
Dim wb   As Workbook
Dim wsM  As Worksheet
Dim wsD  As Worksheet
Dim MyV  As Double
Dim Rw   As Long
Dim FR   As Long
Dim LR   As Long
Dim rFnd As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
Set wb = Workbooks.Open("C:\Data\Full Master Data.xlsm")
Set wsM = wb.Sheets("Master sheet")

With ThisWorkbook.Sheets("Data input")
    .Range("D:E").MergeCells = False
    FR = .Range("D:D").Find("Name", LookIn:=xlValues, LookAt:=xlWhole).Row
    LR = .Range("D" & .Rows.Count).End(xlUp).Row
    
    For Rw = FR To LR
        MyV = .Range("E" & Rw)
        With .Range("D" & Rw)
            If .Value <> "Name" And .Value <> "" And MyV > 0 Then
                Set rFnd = wsM.Range("A:A").Find(.Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not rFnd Is Nothing Then
                    rFnd.Offset(, 1).Resize(, 9).Value = rFnd.Offset(, 2).Resize(, 9).Value
                    rFnd.Offset(, 10).Value = MyV
                    Set rFnd = Nothing
                End If
            End If
        End With
    Next Rw
End With

wb.Close True
Application.ScreenUpdating = True
End Sub

[ad9051]

Brilliant! It seems to be quite a laggy/slow macro, but it does the job! Marked as solved thankyou very very much!

All the best Alan