Define a list of files as a variable target (VBA)

secoo140

Board Regular
Joined
Oct 12, 2013
Messages
83
Office Version
2010
Platform
Windows
Hello,

I use a code to copy files to a template file, and delete old one, then save as old file's name.

But choosing every single file for like 500 files, is very boring.

Since, I do this operation once a week, now I need more clever way to do it.

I use "GetOpenFilename" in order to choose target and source.

and I have the exact path list of source.

"
D:\EDU\TRT\Y-90\0481_NEBAHAT_GUZEL_LK\0481_NEBAHAT_GUZEL.xlsm


D:\EDU\TRT\Y-90\0482_ARIF_GULEREV_TRT\0482_ARIF_GULEREV.xlsm

"

thanks


VBA Code:
Sub fname2()

fname1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls?", , "yeni - hedef dosyayı seçelim")
If fname1 = "False" Then Exit Sub

For yeniA = 1 To 10

fname = Application.GetOpenFilename("Excel Files (*.xls*), *.xls?", , "eski - kaynak dosyayı seçelim")
If fname = "False" Then Exit Sub


Set w2 = Workbooks.Open(fname)
Set s2 = w2.Sheets(1)
Set w3 = Workbooks.Open(fname1)

w2.Activate

For Each sh In Worksheets
    sh.Unprotect "sb123"
Next

ActiveSheet.Cells.UnMerge

w2.Worksheets("kanlar").Range("A2:N50").Copy
w3.Worksheets("kanlar").Range("A2").PasteSpecial Paste:=xlPasteFormulas
 
w2.Worksheets("doz").Range("A2:H10").Copy
w3.Worksheets("doz").Range("A2").PasteSpecial Paste:=xlPasteValues
 
w2.Worksheets("Görüntülemeler").Range("A2:F50").Copy
w3.Worksheets("Görüntülemeler").Range("A2").PasteSpecial Paste:=xlPasteValues
 
w2.Worksheets("Dozimetri").Range("A2:T50").Copy
w3.Worksheets("Dozimetri").Range("A2").PasteSpecial Paste:=xlPasteValues
 
w2.Worksheets("Konsey_ekibi").Range("A2:J100").Copy
w3.Worksheets("Konsey_ekibi").Range("A2").PasteSpecial Paste:=xlPasteValues
 
w2.Worksheets("kimlik").Range("C1").Copy
w3.Worksheets("kimlik").Range("C1").PasteSpecial Paste:=xlPasteValues
 
    w2.Worksheets("kimlik").Range("C2").Copy
  w3.Worksheets("kimlik").Range("C2").PasteSpecial Paste:=xlPasteValues
 
    w2.Worksheets("kimlik").Range("C3").Copy
  w3.Worksheets("kimlik").Range("C3").PasteSpecial Paste:=xlPasteValues
 
    w2.Worksheets("kimlik").Range("C5").Copy
  w3.Worksheets("kimlik").Range("C5").PasteSpecial Paste:=xlPasteValues
    
    w2.Worksheets("kimlik").Range("H1").Copy
  w3.Worksheets("kimlik").Range("H1").PasteSpecial Paste:=xlPasteValues
 
    w2.Worksheets("kimlik").Range("H2").Copy
  w3.Worksheets("kimlik").Range("H2").PasteSpecial Paste:=xlPasteValues
 
    w2.Worksheets("kimlik").Range("H3").Copy
  w3.Worksheets("kimlik").Range("H3").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("B9").Copy
  w3.Worksheets("kimlik").Range("B9").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("D7").Copy
  w3.Worksheets("kimlik").Range("D7").PasteSpecial Paste:=xlPasteValues
 
        w2.Worksheets("kimlik").Range("D9").Copy
  w3.Worksheets("kimlik").Range("D9").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("F7").Copy
  w3.Worksheets("kimlik").Range("F7").PasteSpecial Paste:=xlPasteValues
 
        w2.Worksheets("kimlik").Range("F9").Copy
  w3.Worksheets("kimlik").Range("F9").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("H7").Copy
  w3.Worksheets("kimlik").Range("H7").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("H9").Copy
  w3.Worksheets("kimlik").Range("H9").PasteSpecial Paste:=xlPasteValues
 
        w2.Worksheets("kimlik").Range("J9").Copy
  w3.Worksheets("kimlik").Range("J9").PasteSpecial Paste:=xlPasteValues
 
        w2.Worksheets("kimlik").Range("J7").Copy
  w3.Worksheets("kimlik").Range("J7").PasteSpecial Paste:=xlPasteValues
 
          w2.Worksheets("kimlik").Range("F11").Copy
  w3.Worksheets("kimlik").Range("F11").PasteSpecial Paste:=xlPasteValues
 
          w2.Worksheets("kimlik").Range("I11").Copy
  w3.Worksheets("kimlik").Range("I11").PasteSpecial Paste:=xlPasteValues
 
'EX TARİHİ
          w2.Worksheets("kimlik").Range("I5").Copy
w3.Worksheets("kimlik").Range("I5").PasteSpecial Paste:=xlPasteValues

'HİKAYE
    w2.Worksheets("kimlik").Range("B19:B23").Copy
  w3.Worksheets("kimlik").Range("B19:B23").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("B28:B32").Copy
  w3.Worksheets("kimlik").Range("B28:B32").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("E19:E23").Copy
  w3.Worksheets("kimlik").Range("E19:E23").PasteSpecial Paste:=xlPasteValues
 
      w2.Worksheets("kimlik").Range("E28:E32").Copy
  w3.Worksheets("kimlik").Range("E28:E32").PasteSpecial Paste:=xlPasteValues
 
'PATOLOJİ
    w2.Worksheets("kimlik").Range("A39").Copy
w3.Worksheets("kimlik").Range("A39").PasteSpecial Paste:=xlPasteValues
 'OYKÜ
 
  w3.Worksheets("kimlik").oyku1.Value = w2.Worksheets("kimlik").oyku1.Value
 
  Application.CutCopyMode = False

w2.Close 0
Kill fname
w3.Worksheets("Formlar").Select
w3.SaveAs Filename:=fname
w3.Close 0

Next yeniA

End Sub
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
824
Hello,
Were you looking to loop through files in a folder and perform those same actions?
 

secoo140

Board Regular
Joined
Oct 12, 2013
Messages
83
Office Version
2010
Platform
Windows
Hello,
Were you looking to loop through files in a folder and perform those same actions?
Yes.

if the variable "fname" choose its path from the workbook I use,
Worksheets("liste"). range("a2 : a -last")
 

Watch MrExcel Video

Forum statistics

Threads
1,102,821
Messages
5,489,068
Members
407,668
Latest member
MODELXLS

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top