VBA code for copying columns from one worksheet to another when source file is keeps changing

[reshmavarma]

I have to copy column A, C and D from source file (sheet name"Input") to column A,B and C in destination file (sheet "sections"). But the source file name keeps changing every time new data is recorded. Can i use workbooks(1) and workbooks(2) instead of specifying workbook name if these are the only two files open.
Can someone help me out with a simple code for this. Im a beginner.

 

[mumps]

Is the name of the destination file always the same? What is the full name of the destination file?

 

[reshmavarma]

Yes the destination file remains the same. File name is Flange Inspection report.
Is there away to use a pop box so the user can type in the source file name? Before the code runs
That way I’ll be able to reuse the code.

 

[mumps]

Do you already have a macro? If so, please post a copy. Is the destination file name "Flange Inspection report.xlsm"? I assume your macro is in the destination file. Is this correct?

 

[reshmavarma]

the code I am using right now is

Sub CopyColumnsToOtherWorkbook()

Dim srcBook As Workbook
Dim destBook As Workbook
Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Set srcBook = Workbooks("EaylinkFile10/09/19")
Set destBook = Workbooks("FlangeInspectionreport")
Set srcSheet = srcBook.Worksheets("section1")
Set destSheet = destBook.Worksheets("Mids")
srcSheet.Range("B2:B32").Copy
destSheet.Range("D3:D33").PasteSpecial (xlPasteValues)
srcSheet.Range("D2:D32").Copy
destSheet.Range("H3:H33").PasteSpecial (xlPasteValues)


End Sub

 

[reshmavarma]

sorry
in the code .xlsx and .xlsm extensions are missing

 

[mumps]

Place this macro in the destination workbook. Save the destination workbook as a macro-enabled file. Make sure both workbooks are open and run the macro.

Sub CopyColumnsToOtherWorkbook()
    Application.ScreenUpdating = False
    Dim srcSheet As Worksheet, destSheet As Worksheet, WB As Workbook
    For Each WB In Application.Workbooks
        If WB.Name <> ThisWorkbook.Name Then
            Set srcSheet = WB.Sheets("section1")
        End If
    Next WB
    Set destSheet = ThisWorkbook.Sheets("Mids")
    With srcSheet
        .Range("B2:B32").Copy
        destSheet.Range("D3").PasteSpecial xlPasteValues
        .Range("D2:D32").Copy
        destSheet.Range("H3").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[reshmavarma]

Thank you
but when I open the destination file my code doesn't run automatically
I need to go to the editor and run the program.

 

[mumps]

Remove the macro from the standard module in the destination workbook. Then place the macro below in the code module for ThisWorkbook in the destination workbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the destination workbook as a macro-enabled file and close it. With the source file already open, re-open the destination file and the macro will run automatically.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim srcSheet As Worksheet, destSheet As Worksheet, WB As Workbook
    For Each WB In Application.Workbooks
        If WB.Name <> ThisWorkbook.Name Then
            Set srcSheet = WB.Sheets("section1")
        End If
    Next WB
    Set destSheet = ThisWorkbook.Sheets("Mids")
    With srcSheet
        .Range("B2:B32").Copy
        destSheet.Range("D3").PasteSpecial xlPasteValues
        .Range("D2:D32").Copy
        destSheet.Range("H3").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub