opening and copy/pasting variable named file

Question

I have the following vba code which opens a Folder, where i save the Excel files with certain names ("11.xlsm and "2.xlsm") and then open These files automatically and copy their Sheets called "data" and paste it to my masterworkbook "makrotochange".

 ChDir _
    "Z:\1000_Entwicklung\05_PROJECT\0558000_CFT\055800L_CFT_Projektleitung\99_Arbeitsordner PL\Tanverdi, Yigit\SAA"
Workbooks.Open Filename:= _
    "Z:\1000_Entwicklung\05_PROJECT\0558000_CFT\055800L_CFT_Projektleitung\99_Arbeitsordner PL\Tanverdi, Yigit\SAA\11.xlsm"
Workbooks.Open Filename:= _
    "Z:\1000_Entwicklung\05_PROJECT\0558000_CFT\055800L_CFT_Projektleitung\99_Arbeitsordner PL\Tanverdi, Yigit\SAA\2.xlsm"
Windows("makrotochange.xlsm").Activate



 Windows("11.xlsm").Activate
sheets("data").Select

sheets("data").Move After:=Workbooks("makrotochange.xlsm").sheets(23)

Windows("2.xlsm").Activate

sheets("data").Select
sheets("data").Move After:=Workbooks("makrotochange.xlsm").sheets(24)

I want to be able to open any amount of variable named files which is in

"Z:\1000_Entwicklung\05_PROJECT\0558000_CFT\055800L_CFT_Projektleitung\99_Arbeitsordner PL\Tanverdi, Yigit\SAA"

and copy/paste it to my "makrotochange.xlsm" masterworkbook.

How can i do this ?

See this: http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — Mrig, May 11 '16 at 08:21
Actually, i think that link will help you more than you think. The link shows code that will open all files in a folder if they have the word test in the name. If you replace the "IF" statement in the middle of the loop with your copy/paste code, you will have the code to loop through every file in a folder, copy from it, paste to another, and close. — DDuffy, May 11 '16 at 09:13

DDuffy · Accepted Answer · 2016-05-11T10:39:47.703

Untested, as i am currently unable to use Excel. However, using the information you provided, and the link @Mrig provided, i believe this could be the base for what you're looking for.

    Sub LoopThroughFiles()
Dim StrFile As String
Dim WB As Workbook
Dim InputFilePath As String
InputFilePath = "Z:\1000_Entwicklung\05_PROJECT\0558000_CFT\055800L_CFT_Projektleitung\99_Arbeitsordner PL\Tanverdi, Yigit\SAA\"
StrFile = Dir(InputFilePath & "*")

Do While Len(StrFile) > 0
        Set WB = Workbooks.Open(InputFilePath & StrFile)
        WB.Activate
            Sheets("data").Select
            Sheets("data").Move After:=Workbooks("makrotochange.xlsm").Sheets(23)
            StrFile = Dir()
    Loop
    End Sub

EDITED

Edited the code now that i am able to use Excel.

This will take all the files in the folder, open them, copy the "Data" sheet, and move it to after sheet 23 in your makrotochange.xlsm file.

A few points of interest though. You need to make sure that your makrotochange.xlsm file is open to begin with. You need to make sure all files have a sheet called "Data". They will be pasted after Sheet 23. So the last file to be pasted in will be the one next to sheet 23.

Have fun

thank you for the help. However this does not compile, as it states Workbooks.Close (False) causes Problem. when i delete this then it gives another error. — Yigit Tanverdi, May 11 '16 at 09:40

opening and copy/pasting variable named file

1 Answers1