Retrieving data from one workbook to another

[kelly mort]

I followed this post :
https://www.mrexcel.com/forum/excel-questions/1005007-standard-entry-another-worksheet.html

to get my data being able to be uploaded to the database file on a network successfully. Now I want a way i can get the data from the database file back to a macro enabled workbook. I am stacked and need help.

Thanks in advance
Kelly

 

[kelly mort]

During my search, I came across this script and I want the bigger brains help me to amend it for my use. I will copy the data from one workbook that's macro free and then paste it into another workbook that's is macro enabled. I don't know if I have to open it before I copy or there is a way I can copy without opening. Anything flexible will go a long way to help me.

Sub WorkbookLoop()
    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim templateWb As Workbook
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row

    Workbooks.Open masterWb.Range("E1").Value & Range("C1").Value
    Set templateWb = ActiveWorkbook
    
    Application.DisplayAlerts = False
    On Error GoTo MissWB
    For i = 4 To finalRow
        sPath = masterWb.Range("B" & i).Value
        sFileName = masterWb.Range("A" & i).Value
        
        If ThisWorkbook.Sheets(1).Range("G" & i) <> "File Available. Data Copied" Then
            Workbooks.Open Filename:=sPath & sFileName
            
            For j = i To finalRow
            
                If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                    Range(masterWb.Range("D" & j).Value).Copy _
                        Destination:=templateWb.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                    master.Range("G" & j).Value = "File Available. Data Copied"
                    
                End If
                
            Next j
            Workbooks(sFileName).Close SaveChanges:=False
            
        End If
        
NextWB:
    Next i
    
    On Error GoTo 0
    
    MsgBox "Done."
    
    Set masterWb = Nothing
    Application.DisplayAlerts = True
    Exit Sub

    
MissWB:
    masterWb.Range("G" & i).Value = "File Not Available"
    Resume NextWB
End Sub

 

[dmt32]

Have a look here:https://www.mrexcel.com/forum/excel...late-non-contiguous-cells-array-all-once.html

and see if any help to you.

Dave

 

[kelly mort]

Thanks Dave.
I am looking at the script. I don't really understand it that much. I will be glad you can explain the logic behind it for me here:

Thanks again
Kelly

 

[dmt32]

Thanks Dave.
I am looking at the script. I don't really understand it that much. I will be glad you can explain the logic behind it for me here:

Thanks again
Kelly

DatabaseToDataEntry code returns a contiguous range of values from a worksheet (database) to non- contiguous range of cells (input form)

DatabaseToDataEntry Code has two parameters

- InputFormRange As Range The range of cells in your data entry entry form

- Target As Range The Range to be returned in your database (only the first cell needs to be found in the range)

to return required database record to your data entry form

DatabaseToDataEntry DataEntryRange, Foundcell

Where DataEntryRange is the range of input cells & FoundCell is the required cell in your database.

Dave

 

[kelly mort]

Hello Dave,
I ran the script and had an error on the line :

Set Foundcell = Worksheets("DATABASE").Range("B2")

Says run time error 9
Subscript out of range.

Also I am finding it difficult to understand the line:

Set DataEntryRange = ThisWorkbook.Worksheets("Entry Sheet").Range("C6,C8,F6,C10,G8,H12,D12")

What does this do?:

Range("C6,C8,F6,C10,G8,H12,D12")

Regards
Kelly