Transferring Data Between Two Open Workbooks

[NorthbyNorthwest]

Hi, everyone. I want an end user to be able to transfer data from one workbook to a duplicate workbook. The source workbook is where the code is stored. So, it can be referenced as this workbook or the active workbook. However, the name of the target or destination workbook is unknown to me (I'm not precisely sure what the end user might name it). Although I can assume a couple of words will be it: "Audit Tracker."

I also don't know where the end user will store the target/destination workbook. So, I'm trying to create code in which both workbooks must be open during the transfer. How can I reference the destination workbook without an exact name? Can I refer to it as <>this workbook? Or how might I use a partial name?

Any help appreciated.

 

[Cubist]

One idea is to open file explorer and have the user select the file and store the name. Something like this.

Sub StoreFileName()
    Dim fileDialog As FileDialog
    Dim selectedFileName As String
    
    Set fileDialog = Application.FileDialog(msoFileDialogFilePicker)
    fileDialog.Title = "Select a File"
    
    If fileDialog.Show = -1 Then
        selectedFileName = fileDialog.SelectedItems(1)
        MsgBox "Selected file: " & selectedFileName
    Else
        MsgBox "No file selected"
    End If
End Sub

 

[NorthbyNorthwest]

Thanks, Cubist. I've been trying how to make your suggestion work. See code below.

Sub DuplicateDetails()
Dim wb As Workbook
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Dim FolderName As String
Dim wsDest As Worksheet
Dim FinalRow As Long
Dim DestLastRow As Long
Dim wsCopy As Worksheet
Dim bScreenUpdating As Boolean
Dim fileDialog As fileDialog
Dim selectedFileName As String
Dim rngCopy1 As Range
Dim rngCopy2 As Range
Dim rngCopy3 As Range
Dim msg As String

'Copy audits from Audit Tracker workbook [Audit Details worksheet]
'Paste audits to another Audit Tracker workbook [Audit Details worksheet]

Set wkbSource = ThisWorkbook

Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
fileDialog.Title = "Select a File"
    
If fileDialog.Show = -1 Then
    selectedFileName = fileDialog.SelectedItems(1)
    MsgBox "Selected file: " & selectedFileName
Else
    MsgBox "No file selected"
End If

Set wkbDest = Workbooks.Open(selectedFileName)


'Reduce screen flicker
bScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

'Start copy job
Set wsCopy = ThisWorkbook.Sheets("Audit Details")
    With wsCopy
        FinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
        'Audit Tracker columns B:BD, CU:CV, CZ:DX
        Set rngCopy1 = .Range(.Cells(6, "B"), .Cells(FinalRow, "BD"))
        Set rngCopy2 = .Range(.Cells(6, "CU"), .Cells(FinalRow, "CV"))
        Set rngCopy3 = .Range(.Cells(6, "CZ"), .Cells(FinalRow, "DX"))
    End With
    
Set wsDest = wkbDest.Worksheets("Audit Details")
    DestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Row
    'Audit Tracker columns B:BD, BE:BF, BJ:CH
    rngCopy1.Copy
    wsDest.Range("B" & DestLastRow).PasteSpecial Paste:=xlPasteValues
    rngCopy2.Copy
    wsDest.Range("BE" & DestLastRow).PasteSpecial Paste:=xlPasteValues
    rngCopy3.Copy
    wsDest.Range("BJ" & DestLastRow).PasteSpecial Paste:=xlPasteValues
 
Application.GoTo wsDest.Range("A1")

msg = MsgBox("Audit Details successfully copied to duplicate workbook.", vbInformation, "Mission Accomplished")

Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = bScreenUpdating
End Sub

 

[Cubist]

What's wrong with the code?

 

[NorthbyNorthwest]

What's wrong with the code?

Sorry, I get runtime error when I get to the copy/paste section of the three sections. See uploaded image.

 

[NorthbyNorthwest]

Sorry, I get runtime error when I get to the copy/paste section of the three sections. See uploaded image.

So, errors says sheets protected. But neither "Audit Details" sheet is protected. There are other worksheets protected. But not this particular one. Also the workbook itself is protected. But why would that stop me from copying and pasting?

 

[NorthbyNorthwest]

So, errors says sheets protected. But neither "Audit Details" sheet is protected. There are other worksheets protected. But not this particular one. Also the workbook itself is protected. But why would that stop me from copying and pasting?

So, I've been reviewing code and realized I referenced wrong destination cells. I addressed my mistakes. The code is now working, except I think I need help with finding last row in destination worksheet. I am copying data from one listobject ("tblDetails") to another listobject ("tblDetails") in different workbooks. Could you help in this area?