Modify and specify two open files

[elmnas]

Hello Excel people,

I have made following code the code is very simple,
the script copying certain columns from one place to another file.

I open the SourceFile.xls and import my.bas file to.

when you run the script you get a windowdialog you can selecta folder the script open the file (anything)_TSP.xls

it always copyingdata from the TargetFile.xls and paste into the Sourcefile.
I have now hardcoded the filenames but could someone help me to change it?
cause I always import the my.bas to the sourcefile so that is the sourcefile.


See code below

Sub VMergeColumns()


Dim myPath As String
Dim StrCurrentfile As String
Dim StrFName As String
Dim myLangFile As String
Dim intResult As Integer




Application.DisplayAlerts = True


intResult = Application.FileDialog(msoFileDialogFolderPicker).Show
    If intResult = 0 Then
        MsgBox "User pressed cancel macro will stop!"
    Exit Sub
    
        Else
        
        MyOrGPathFile = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
        
     End If


StrCurrentfile = Dir(MyOrGPathFile & "*_TSP.xls")
Do While StrCurrentfile <> ""


myfile = MyOrGPathFile & StrCurrentfile


'MsgBox myfile
Set myTFile = Workbooks.Open(MyOrGPathFile & StrCurrentfile)


Columns(1).Select
Selection.Copy


Workbooks("SourceFile.xls").Activate
Columns("A:A").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "TT Number"
ActiveCell.Interior.ColorIndex = xlNone


Workbooks("TargetFile_TSP.xls").Activate
Columns("D:D").Select
Selection.Copy


Workbooks("SourceFile.xls").Activate
Columns("I:I").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "Source language"
ActiveCell.Interior.ColorIndex = xlNone


Workbooks("TargetFile_TSP.xls").Activate
Columns("E:E").Select
Selection.Copy


Workbooks("SourceFile.xls").Activate
Columns("J:J").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "Target Language"
ActiveCell.Interior.ColorIndex = xlNone


Workbooks("TargetFile_TSP.xls").Activate
Columns("G:G").Select
Selection.Copy


Workbooks("SourceFile.xls").Activate
Columns("K:K").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "No of New words"
ActiveCell.Interior.ColorIndex = xlNone


Workbooks("TargetFile_TSP.xls").Activate
Columns("H:H").Select
Selection.Copy


Workbooks("SourceFile.xls").Activate
Columns("L:L").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "No of Fuzzy matches"
ActiveCell.Interior.ColorIndex = xlNone


Workbooks("TargetFile_TSP.xls").Activate
Columns("I:I").Select
Selection.Copy


Workbooks("SourceFile.xls").Activate
Columns("M:M").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "No of 100% match And Repetitions"
ActiveCell.Interior.ColorIndex = xlNone


Workbooks("TargetFile_TSP.xls").Activate
Columns("L:L").Select
Selection.Copy


Workbooks("SourceFile.xls").Activate
Columns("V:V").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "No of 100% match And Repetitions"
ActiveCell.Interior.ColorIndex = xlNone




'Set noLangFilen = Workbooks.Open(MyOrGPathFile & MyFile)
'Application.DisplayAlerts = False
'noLangFilen.CheckCompatibility = False
'noLangFilen.Close SaveChanges:=True
StrCurrentfile = Dir


Loop




End Sub

Thank you in advance

 

[RickXL]

Hi,

I can see what your macro is doing but could you provide more details about what you want to change, please?

Do you want the macro to do something different, or to be more flexible or just to be run in a different way?

Thanks.