SOLVED
Home

Loop through Multiple files in share folder

%3CLINGO-SUB%20id%3D%22lingo-sub-660988%22%20slang%3D%22en-US%22%3ELoop%20through%20Multiple%20files%20in%20share%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-660988%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20build%20a%20code%20which%20loops%20through%20multiple%20files%20in%20a%20folder%20and%20copy%20and%20paste%20the%20data%20to%20a%20template%20and%20drag%20formulas%20for%2026%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20able%20to%20build%20code%20to%20open%20one%20file%20from%20source%20folder%20and%20copy%20paste%20data%20to%20a%20template%20and%20then%20save%20as%20template%20in%20another%20folder%20based%20on%20source%20file%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20help%20on%20how%20to%20loop%20through%20all%20the%20files%20in%20a%20folder.%3C%2FP%3E%3CP%3EBelow%20is%20my%20existing%20code%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20Sub%20OIM()%3C%2FP%3E%3CP%3EDim%20a%2C%20b%20As%20String%3CBR%20%2F%3ERange(%22A1%22).Select%3CBR%20%2F%3Ea%20%3D%20ActiveCell.Value%3CBR%20%2F%3ERange(%22A2%22).Select%3CBR%20%2F%3Eb%20%3D%20ActiveCell.Value%3CBR%20%2F%3EWorkbooks.Open%20(a)%3CBR%20%2F%3EWorkbooks.Open%20(b)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange(%22A2%3AAI2%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3EWindows(%22OIM%20ResultFile_Template%20Final.xlsb%22).Activate%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3C%2FP%3E%3CP%3ERange(%22AJ7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2035).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AJ8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AK7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2036).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AK8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E'%20Range(%22AK6%22).Select%3CBR%20%2F%3E'%20ActiveCell.Value%20%3D%20%22Rule%201%20-%20CC%20GL%20%26amp%3B%20PC%20%2F%20LOB%22%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AL7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2037).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AL8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AM7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2038).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AM8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AN7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2039).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AN8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22Ao7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2040).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22Ao8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AP7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2041).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22Ap8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AQ7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2042).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AQ8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AR7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2043).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AR8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AS7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2044).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AS8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AT7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2045).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AT8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3E%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AU7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2046).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AU8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AV7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2047).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AV8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AW7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2048).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AW8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AX7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2049).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AX8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22Ay7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2050).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22Ay8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22AZ7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2051).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22AZ8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BA7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2052).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BA8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BB7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2053).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BB8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BC7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2054).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BC8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BD7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2055).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BD8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BE7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2056).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BE8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BF7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2057).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BF8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BG7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2058).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BG8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BH7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2059).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BH8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BI7%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A7%22).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%2060).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlUp)).Select%3CBR%20%2F%3EActiveSheet.PasteSpecial%3CBR%20%2F%3ERange(%22BI8%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValuesAndNumberFormats%2C%20Operation%3A%3D%20_%3CBR%20%2F%3ExlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20path%20As%20String%3CBR%20%2F%3EDim%20filename%20As%20String%3CBR%20%2F%3Epath%20%3D%20%22%5C%5Cie3bst0003%5CHGFC_Finance_OPS%5C5.%20Integrations%5CDaily%20Dash%20Boards%5CGeneral%20Accounting%5COIM%20Clearing%5C%22%3CBR%20%2F%3Efilename%20%3D%20Range(%22A7%22)%3CBR%20%2F%3EActiveWorkbook.SaveAs%20filename%3A%3Dpath%20%26amp%3B%20filename%20%26amp%3B%20%22.xls%22%2C%20FileFormat%3A%3DxlOpenXMLWorkbook%2C%20CreateBackup%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EActiveWorkbook.Save%3CBR%20%2F%3EActiveWindow.Close%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20False%3CBR%20%2F%3EActiveWindow.Close%3CBR%20%2F%3E%3CBR%20%2F%3EMsgBox%20%22Its%20done%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-660988%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-661356%22%20slang%3D%22en-US%22%3ERe%3A%20Loop%20through%20Multiple%20files%20in%20share%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-661356%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152352%22%20target%3D%22_blank%22%3E%40Chandrakanth%20K%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Eyou%20can%20use%20this%20macro%20to%20do%20similar%20things..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20DateinAusVerzeichnisVerarbeiten()%3CBR%20%2F%3EDim%20strOrdner%20As%20String%3CBR%20%2F%3EDim%20strDatei%20As%20String%3CBR%20%2F%3EDim%20wkbQuelle%20As%20Workbook%3CBR%20%2F%3EDim%20lngZeileFrei%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3E'PFad%20anpassen%3CBR%20%2F%3EstrOrdner%20%3D%20ThisWorkbook.Path%20%26amp%3B%20%22%5CDaten%5C%22%3CBR%20%2F%3EstrDatei%20%3D%20Dir(strOrdner%20%26amp%3B%20%22*.xls*%22)%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Sheet1%3CBR%20%2F%3E.Range(%22A2%3AB%22%20%26amp%3B%20.Rows.Count).Clear%3CBR%20%2F%3EDo%20While%20strDatei%20%26lt%3B%26gt%3B%20%22%22%3CBR%20%2F%3E'Debug.Print%20strDatei%3CBR%20%2F%3ESet%20wkbQuelle%20%3D%20Workbooks.Open(strOrdner%20%26amp%3B%20strDatei)%3CBR%20%2F%3ElngZeileFrei%20%3D%20.Range(%22A%22%20%26amp%3B%20.Rows.Count).End(xlUp).Row%20%2B%201%3CBR%20%2F%3EwkbQuelle.Worksheets(%22tbl_Preise%22).Range(%22A1%3AB5%22).Copy%20_%3CBR%20%2F%3EDestination%3A%3D.Range(%22A%22%20%26amp%3B%20lngZeileFrei)%3CBR%20%2F%3ElngZeileFrei%20%3D%20.Range(%22A%22%20%26amp%3B%20.Rows.Count).End(xlUp).Row%20%2B%201%3CBR%20%2F%3EwkbQuelle.Worksheets(%22Sheet1%22).Range(%22A1%3AB1%22).Copy%20_%3CBR%20%2F%3EDestination%3A%3D.Range(%22A%22%20%26amp%3B%20lngZeileFrei)%3CBR%20%2F%3EwkbQuelle.Close%20savechanges%3A%3DFalse%3CBR%20%2F%3EstrDatei%20%3D%20Dir%3CBR%20%2F%3ELoop%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-Tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%3C%2FA%3E%20-%20the%20database%20full%20of%20macros%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-716975%22%20slang%3D%22en-US%22%3ERe%3A%20Loop%20through%20Multiple%20files%20in%20share%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716975%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20to%20get%20this%20code%20in%20English%3F%20Finding%20it%20difficult%20to%20understand%20the%20terms%2Fwords%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EChandrakanth.K%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717111%22%20slang%3D%22en-US%22%3ERe%3A%20Loop%20through%20Multiple%20files%20in%20share%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152352%22%20target%3D%22_blank%22%3E%40Chandrakanth%20K%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eok.%3C%2FP%3E%3CPRE%3ESub%20AllFilesInFolder()%3CBR%20%2F%3EDim%20strPath%20As%20String%3CBR%20%2F%3EDim%20strFile%20As%20String%3CBR%20%2F%3EDim%20wkbSource%20As%20Workbook%3CBR%20%2F%3EDim%20lngRowFree%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3E'adopt%20path%3CBR%20%2F%3EstrPath%20%3D%20ThisWorkbook.Path%20%26amp%3B%20%22%5CData%5C%22%3CBR%20%2F%3EstrFile%20%3D%20Dir(strPath%20%26amp%3B%20%22*.xls*%22)%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Sheet1%3CBR%20%2F%3E.Range(%22A2%3AB%22%20%26amp%3B%20.Rows.Count).Clear%3CBR%20%2F%3EDo%20While%20strFile%20%26lt%3B%26gt%3B%20%22%22%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20wkbSource%20%3D%20Workbooks.Open(strPath%20%26amp%3B%20strFile)%3CBR%20%2F%3ElngRowFree%20%3D%20.Range(%22A%22%20%26amp%3B%20.Rows.Count).End(xlUp).Row%20%2B%201%3CBR%20%2F%3EwkbSource.Worksheets(%22tbl_Preise%22).Range(%22A1%3AB5%22).Copy%20_%3CBR%20%2F%3EDestination%3A%3D.Range(%22A%22%20%26amp%3B%20lngRowFree)%3CBR%20%2F%3ElngRowFree%20%3D%20.Range(%22A%22%20%26amp%3B%20.Rows.Count).End(xlUp).Row%20%2B%201%3CBR%20%2F%3EwkbSource.Worksheets(%22Sheet1%22).Range(%22A1%3AB1%22).Copy%20_%3CBR%20%2F%3EDestination%3A%3D.Range(%22A%22%20%26amp%3B%20lngRowFree)%3CBR%20%2F%3EwkbSource.Close%20savechanges%3A%3DFalse%3CBR%20%2F%3EstrFile%20%3D%20Dir%3CBR%20%2F%3ELoop%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Chandrakanth K
Contributor

Hello All,

 

I need to build a code which loops through multiple files in a folder and copy and paste the data to a template and drag formulas for 26 columns.

 

I was able to build code to open one file from source folder and copy paste data to a template and then save as template in another folder based on source file name.

 

Need help on how to loop through all the files in a folder.

Below is my existing code

 

Public Sub OIM()

Dim a, b As String
Range("A1").Select
a = ActiveCell.Value
Range("A2").Select
b = ActiveCell.Value
Workbooks.Open (a)
Workbooks.Open (b)

 

Range("A2:AI2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("OIM ResultFile_Template Final.xlsb").Activate
Range("A7").Select
ActiveSheet.Paste

Range("AJ7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 35).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AJ8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AK7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 36).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AK8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
' Range("AK6").Select
' ActiveCell.Value = "Rule 1 - CC GL & PC / LOB"

Range("AL7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 37).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AL8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AM7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 38).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AM8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Range("AN7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 39).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AN8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Range("Ao7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 40).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("Ao8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AP7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 41).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("Ap8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AQ7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 42).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AQ8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AR7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 43).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AR8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AS7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 44).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AS8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AT7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 45).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AT8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AU7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 46).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AU8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AV7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 47).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AV8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AW7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 48).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AW8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Range("AX7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 49).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AX8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("Ay7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 50).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("Ay8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("AZ7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 51).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("AZ8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BA7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 52).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BA8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BB7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 53).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BB8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BC7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 54).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BC8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BD7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 55).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BD8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BE7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 56).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BE8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BF7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 57).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BF8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BG7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 58).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BG8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BH7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 59).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BH8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("BI7").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 60).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.PasteSpecial
Range("BI8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False



Dim path As String
Dim filename As String
path = "\\ie3bst0003\HGFC_Finance_OPS\5. Integrations\Daily Dash Boards\General Accounting\OIM Clearing\"
filename = Range("A7")
ActiveWorkbook.SaveAs filename:=path & filename & ".xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False




ActiveWorkbook.Save
ActiveWindow.Close

Application.DisplayAlerts = False
ActiveWindow.Close

MsgBox "Its done"

 

End Sub

 

3 Replies

@Chandrakanth K 

Hi,

you can use this macro to do similar things..

 

Sub DateinAusVerzeichnisVerarbeiten()
Dim strOrdner As String
Dim strDatei As String
Dim wkbQuelle As Workbook
Dim lngZeileFrei As Long

'PFad anpassen
strOrdner = ThisWorkbook.Path & "\Daten\"
strDatei = Dir(strOrdner & "*.xls*")

With Sheet1
.Range("A2:B" & .Rows.Count).Clear
Do While strDatei <> ""
'Debug.Print strDatei
Set wkbQuelle = Workbooks.Open(strOrdner & strDatei)
lngZeileFrei = .Range("A" & .Rows.Count).End(xlUp).Row + 1
wkbQuelle.Worksheets("tbl_Preise").Range("A1:B5").Copy _
Destination:=.Range("A" & lngZeileFrei)
lngZeileFrei = .Range("A" & .Rows.Count).End(xlUp).Row + 1
wkbQuelle.Worksheets("Sheet1").Range("A1:B1").Copy _
Destination:=.Range("A" & lngZeileFrei)
wkbQuelle.Close savechanges:=False
strDatei = Dir
Loop

End With

End Sub

 

Regards

Bernd

www.vba-Tanker.com - the database full of macros

Hello @Berndvbatanker 

 

Can you help me to get this code in English? Finding it difficult to understand the terms/words used.

 

Regards,

Chandrakanth.K

Solution

@Chandrakanth K 

ok.

Sub AllFilesInFolder()
Dim strPath As String
Dim strFile As String
Dim wkbSource As Workbook
Dim lngRowFree As Long

'adopt path
strPath = ThisWorkbook.Path & "\Data\"
strFile = Dir(strPath & "*.xls*")

With Sheet1
.Range("A2:B" & .Rows.Count).Clear
Do While strFile <> ""

Set wkbSource = Workbooks.Open(strPath & strFile)
lngRowFree = .Range("A" & .Rows.Count).End(xlUp).Row + 1
wkbSource.Worksheets("tbl_Preise").Range("A1:B5").Copy _
Destination:=.Range("A" & lngRowFree)
lngRowFree = .Range("A" & .Rows.Count).End(xlUp).Row + 1
wkbSource.Worksheets("Sheet1").Range("A1:B1").Copy _
Destination:=.Range("A" & lngRowFree)
wkbSource.Close savechanges:=False
strFile = Dir
Loop

End With

End Sub