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
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies