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

reshmavarma

New Member
Joined
Oct 9, 2019
Messages
5
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Is the name of the destination file always the same? What is the full name of the destination file?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top