abdo meghari
Active Member
- Joined
- Aug 3, 2021
- Messages
- 465
- Office Version
- 2019
Hi Guys,
I heve report publish every month as in file. so become many files across year are existed in the same folder . for instance the file name is Bridgestone Sales Report 05May. 2022 (1) & Bridgestone Sales Report 06 JUNE. 2022 (1) and contains one sheet and contains data like this
so I search for macro create new sheets name based on file name like this Bridgestone Sales Report 05May. 2022 (1) and should copy columns A,B,G should rearrang in location again and ignores the others columns , then the data should be like this
and when run the macro every time just replace data have already existed for the new sheets have already existed based on match files names are existed in the same folder . every file should create new sheet alone and getting data.
the idea should pull the data from multiple files and put in one file for each sheet instead of using manually. will take more time to do that.
thanks
I heve report publish every month as in file. so become many files across year are existed in the same folder . for instance the file name is Bridgestone Sales Report 05May. 2022 (1) & Bridgestone Sales Report 06 JUNE. 2022 (1) and contains one sheet and contains data like this
| Bridgestone Sales Report 05 May 2021.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | RETURNS | SALES | SIZE | MARK | TYPE | ORIGINAL | BRAND | ||
| 2 | 0 | 152 | 1200R20 | BRIDGESTONE | G580 | JAPAN | BS 1200R20 G580 TCF JAP | ||
| 3 | 0 | 94 | 1200R24 | BRIDGESTONE | G582 | JAPAN | BS 1200R24 G582 JAP | ||
| 4 | 0 | 21 | 1400R20 | BRIDGESTONE | VSJ | JAPAN | BS 1400R20 VSJ TCF JAP | ||
| 5 | 0 | 18 | 155R12C | BRIDGESTONE | R623 | JAPAN | BS 155 R12C R623 JAP | ||
| 6 | 4 | 4 | 155R12C | BRIDGESTONE | R624 | INDONESIA | BS 155 R12C R624 INDO | ||
| 7 | 4 | 8 | 165R13C | BRIDGESTONE | R624 | INDONESIA | BS 165 R13C R624 INDO | ||
| 8 | 0 | 4 | 175/70R13 | BRIDGESTONE | EP150 | THAILAND | BS 175/70 R13 EP150 THI | ||
| 9 | 0 | 8 | 175/70R14 | BRIDGESTONE | EP150 | THAILAND | BS 175/70 R14 EP150 THI | ||
| 10 | 4 | 8 | 185/65R14 | BRIDGESTONE | EP150 | INDONESIA | BS 185/65 R14 EP150 INDO | ||
| 11 | 0 | 8 | 185/65R14 | BRIDGESTONE | TEC | THAILAND | BS 185/65 R14 TEC THI | ||
| 12 | 4 | 4 | 185/65R15 | BRIDGESTONE | T005 | INDONESIA | BS 185/65 R15 T005 INDO | ||
| 13 | 4 | 8 | 185/70R13 | BRIDGESTONE | EP150 | INDONESIA | BS 185/70 R13 EP150 INDO | ||
| 14 | 0 | 4 | 185R14C | BRIDGESTONE | R624 | INDONESIA | BS 185R14C R624 INDO | ||
| 15 | 4 | 12 | 195R14C | BRIDGESTONE | 613 | JAPAN | BS 195 R14C 613 JAP | ||
| 16 | 4 | 6 | 195R15C | BRIDGESTONE | R623 | THAILAND | BS 195 R15C R623 THI | ||
| 17 | 4 | 4 | 195/55R16 | BRIDGESTONE | EP30 | THAILAND | BS 195/55 R16 EP300 THI | ||
| 18 | 4 | 12 | 195/60R15 | BRIDGESTONE | EP150 | THAILAND | BS 195/60 R15 EP150 THI | ||
| 19 | 0 | 8 | 195/65R15 | BRIDGESTONE | EP15 | JAPAN | BS 195/65 R15 EP15 JAP | ||
| 20 | 4 | 8 | 195/70R15C | BRIDGESTONE | R623 | JAPAN | BS 195/70 R15C R623 JAP | ||
| 21 | 4 | 4 | 195/75R16C | BRIDGESTONE | R613 | JAPAN | BS 195/75 R16C R613 JAP | ||
| 22 | 0 | 4 | 205/60R16 | BRIDGESTONE | T001 | JAPAN | BS 205/60 R16 T001 JAP | ||
| 23 | 4 | 6 | 205/65R15 | BRIDGESTONE | EP150 | INDONESIA | BS 205/65 R15 EP150 INDO | ||
| 24 | 4 | 4 | 215/45R17 | BRIDGESTONE | T001 | JAPAN | BS 215/45 R17 T001 JAP | ||
| 25 | 4 | 4 | 215/55R16 | BRIDGESTONE | EP30 | INDONESIA | BS 215/55 R16 EP300 INDO | ||
| 26 | 4 | 8 | 215/55R17 | BRIDGESTONE | MY-02 | THAILAND | BS 215/55 R17 MY-02 THI | ||
| 27 | 0 | 4 | 215/55R17 | BRIDGESTONE | T005 | JAPAN | BS 215/55 R17 T005 JAP | ||
| 28 | 4 | 16 | 215/55R17 | BRIDGESTONE | TG90 | INDONESIA | BS 215/55 R17 TG90 INDO | ||
| 29 | 2 | 4 | 215/60R16 | BRIDGESTONE | EP30 | THAILAND | BS 215/60 R16 EP30 THI | ||
| 30 | 4 | 8 | 215/65R16C | BRIDGESTONE | R611 | THAILAND | BS 215/65 R16C R611 THI | ||
| 31 | 4 | 23 | 215/70R15C | BRIDGESTONE | R623 | THAILAND | BS 215/70 R15C R623 THI | ||
| 32 | 0 | 4 | 225/55R16 | BRIDGESTONE | T001 | JAPAN | BS 225/55 R16 T001 JAP | ||
| 33 | 4 | 12 | 225/55R17 | BRIDGESTONE | EP30 | THAILAND | BS 225/55 R17 EP300 THI | ||
| 34 | 0 | 10 | 225/60R16 | BRIDGESTONE | EP30 | JAPAN | BS 225/60 R16 EP300 JAP | ||
| 35 | 0 | 4 | 225/60R16 | BRIDGESTONE | T001 | JAPAN | BS 225/60 R16 T001 JAP | ||
| 36 | 4 | 4 | 225/60R17 | BRIDGESTONE | D-SPORT | JAPAN | BS 225/60 R17 D-SPORT JAP | ||
| 37 | 0 | 8 | 225/70R15C | BRIDGESTONE | R623 | JAPAN | BS 225/70 R15C R623 JAP | ||
| 38 | 4 | 8 | 225/70R16 | BRIDGESTONE | D687 | JAPAN | BS 225/70 R16 D687 JAP | ||
| 39 | 0 | 4 | 225/70R16 | BRIDGESTONE | H005 | THAILAND | BS 225/70 R16 H005 THI | ||
| 40 | 0 | 4 | 225/70R17 | BRIDGESTONE | D697 | THAILAND | BS 225/70 R17 D697 THI | ||
| 41 | 4 | 4 | 235/55R17 | BRIDGESTONE | ER30 | JAPAN | BS 235/55 R17 ER30 JAP | ||
| 42 | 0 | 4 | 235/55R17 | BRIDGESTONE | T005 | THAILAND | BS 235/55 R17 T005 THI | ||
| 43 | 0 | 8 | 235/55R18 | BRIDGESTONE | E031 | JAPAN | BS 235/55 R18 E031 JAP | ||
| 44 | 0 | 4 | 235/60R16 | BRIDGESTONE | T01 | JAPAN | BS 235/60 R16 T01 JAP | ||
| 45 | 4 | 8 | 235/60R17 | BRIDGESTONE | D400 | JAPAN | BS 235/60 R17 D400 JAP | ||
| 46 | 0 | 4 | 245/40ZR20 | BRIDGESTONE | S001 | JAPAN | BS 245/40 ZR20 S001 JAP | ||
| 47 | 0 | 4 | 245/45R19 | BRIDGESTONE | T005 | JAPAN | BS 245/45 R19 T005 JAP | ||
| 48 | 4 | 4 | 245/70R16 | BRIDGESTONE | D684 | THAILAND | BS 245/70 R16 D684 THI | ||
| 49 | 0 | 38 | 245/70R17 | BRIDGESTONE | D684 | JAPAN | BS 245/70 R17 D684 JAP | ||
| 50 | 0 | 4 | 245/75R17 | BRIDGESTONE | 693A | JAPAN | BS 245/75 R17 693A JAP | ||
| 51 | 4 | 4 | 255/70R16 | BRIDGESTONE | H005 | THAILAND | BS 255/70 R16 H005 THI | ||
| 52 | 0 | 4 | 265/50R20 | BRIDGESTONE | D-SPORT | JAPAN | BS 265/50 R20 D-SPORT JAP | ||
| 53 | 0 | 12 | 265/60R18 | BRIDGESTONE | D840 | JAPAN | BS 265/60R18 D840 JAP | ||
| 54 | 0 | 4 | 265/70R18 | BRIDGESTONE | D684 | JAPAN | BS 265/70 R18 D684 JAP | ||
| 55 | 0 | 4 | 275/40R18 | BRIDGESTONE | S007 | JAPAN | BS 275/40 R18 S007 JAP | ||
| 56 | 0 | 4 | 275/40R20 | BRIDGESTONE | D-SPORT | JAPAN | BS 275/40 R20 DSPORT JAP | ||
| 57 | 0 | 4 | 275/45R19 | BRIDGESTONE | ALENZA | JAPAN | BS 275/45 R19 ALENZA JAP | ||
| 58 | 0 | 4 | 275/55R17 | BRIDGESTONE | D-SPORT | JAPAN | BS 275/55 R17 DSPORT JAP | ||
| 59 | 0 | 4 | 275/70R16 | BRIDGESTONE | D694 | JAPAN | BS 275/70 R16 D694 JAP | ||
| 60 | 4 | 4 | 275/70R16 | BRIDGESTONE | H005 | THAILAND | BS 275/70 R16 H005 THI | ||
| 61 | 4 | 8 | 285/60R18 | BRIDGESTONE | T01 | JAPAN | BS 285/60 R18 T01 JAP | ||
| 62 | 4 | 8 | 285/65R17 | BRIDGESTONE | R683 | JAPAN | BS 285/65 R17 R683 JAP | ||
| 63 | 0 | 8 | 315/80R22.5 | BRIDGESTONE | G580 | JAPAN | BS 315/80 R22.5 G580 JAP | ||
| 64 | 0 | 4 | 315/80R22.5 | BRIDGESTONE | R152 | JAPAN | BS 315/80 R22.5 R152 JAP | ||
| 65 | 1 | 0 | 315/80R22.5 | BRIDGESTONE | R184 | JAPAN | BS 315/80 R22.5 R184 JAP | ||
| 66 | 121 | 105 | 385/65R22.5 | BRIDGESTONE | R164 | JAPAN | BS 385/65 R22.5 R164 JAP | ||
| 67 | 0 | 4 | 445/65R22.5 | BRIDGESTONE | R164 | JAPAN | BS 445/65 R22.5 R164 JAP | ||
| 68 | 0 | 16 | 650R16 | BRIDGESTONE | R230 | JAPAN | BS 650R16 R230 JAP | ||
| 69 | 0 | 74 | 750R16 | BRIDGESTONE | R230 | JAPAN | BS 750R16 R230 TCF JAP | ||
| 70 | 0 | 112 | 750R16 | BRIDGESTONE | VSJ | JAPAN | BS 750R16 VSJ TCF JAP | ||
| 71 | 1 | 0 | 8.25R16 | BRIDGESTONE | R180 | JAPAN | BS 8.25R16 R180 JAP | ||
| 72 | 10 | 0 | 315/80R22.5 | BRIDGESTONE | DS50 | THAILAND | Dayton 315/80R22.5 DS50 THI | ||
| 73 | 20 | 2 | 1200R24 | BRIDGESTONE | FSR4000 | JAPAN | FS 1200 R24 FSR4000 JAP | ||
| 74 | 4 | 4 | 205/55R16 | BRIDGESTONE | TZ700 | JAPAN | FS 205/65 R15 TZ700 JAP | ||
| 75 | 4 | 8 | 215/55R17 | BRIDGESTONE | TZ700 | JAPAN | FS 215/55 R17 TZ700 JAP | ||
| 76 | 4 | 4 | 225/45R17 | BRIDGESTONE | TZ700 | JAPAN | FS 225/45 R17 TZ700 JAP | ||
| 77 | 4 | 4 | 31X10.50R15 | BRIDGESTONE | 109S | INDONESIA | FS 31X10.50 R15 DEST AT 109S INDO | ||
| 78 | 0 | 4 | LT285/75R16 | BRIDGESTONE | AT001 | JAPAN | LT285/75 R16 AT001 JAP | ||
Sheet1 | |||||||||
so I search for macro create new sheets name based on file name like this Bridgestone Sales Report 05May. 2022 (1) and should copy columns A,B,G should rearrang in location again and ignores the others columns , then the data should be like this
| REPORT.xlsm | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | ITEM | BRAND | SALES | RETURNS | ||
| 2 | 1 | BS 1200R20 G580 TCF JAP | 152 | 0 | ||
| 3 | 2 | BS 1200R24 G582 JAP | 94 | 0 | ||
| 4 | 3 | BS 1400R20 VSJ TCF JAP | 21 | 0 | ||
| 5 | 4 | BS 155 R12C R623 JAP | 18 | 0 | ||
| 6 | 5 | BS 155 R12C R624 INDO | 4 | 4 | ||
| 7 | 6 | BS 165 R13C R624 INDO | 8 | 4 | ||
| 8 | 7 | BS 175/70 R13 EP150 THI | 4 | 0 | ||
| 9 | 8 | BS 175/70 R14 EP150 THI | 8 | 0 | ||
| 10 | 9 | BS 185/65 R14 EP150 INDO | 8 | 4 | ||
| 11 | 10 | BS 185/65 R14 TEC THI | 8 | 0 | ||
| 12 | 11 | BS 185/65 R15 T005 INDO | 4 | 4 | ||
| 13 | 12 | BS 185/70 R13 EP150 INDO | 8 | 4 | ||
| 14 | 13 | BS 185R14C R624 INDO | 4 | 0 | ||
| 15 | 14 | BS 195 R14C 613 JAP | 12 | 4 | ||
| 16 | 15 | BS 195 R15C R623 THI | 6 | 4 | ||
| 17 | 16 | BS 195/55 R16 EP300 THI | 4 | 4 | ||
| 18 | 17 | BS 195/60 R15 EP150 THI | 12 | 4 | ||
| 19 | 18 | BS 195/65 R15 EP15 JAP | 8 | 0 | ||
| 20 | 19 | BS 195/70 R15C R623 JAP | 8 | 4 | ||
| 21 | 20 | BS 195/75 R16C R613 JAP | 4 | 4 | ||
| 22 | 21 | BS 205/60 R16 T001 JAP | 4 | 0 | ||
| 23 | 22 | BS 205/65 R15 EP150 INDO | 6 | 4 | ||
| 24 | 23 | BS 215/45 R17 T001 JAP | 4 | 4 | ||
| 25 | 24 | BS 215/55 R16 EP300 INDO | 4 | 4 | ||
| 26 | 25 | BS 215/55 R17 MY-02 THI | 8 | 4 | ||
| 27 | 26 | BS 215/55 R17 T005 JAP | 4 | 0 | ||
| 28 | 27 | BS 215/55 R17 TG90 INDO | 16 | 4 | ||
| 29 | 28 | BS 215/60 R16 EP30 THI | 4 | 2 | ||
| 30 | 29 | BS 215/65 R16C R611 THI | 8 | 4 | ||
| 31 | 30 | BS 215/70 R15C R623 THI | 23 | 4 | ||
| 32 | 31 | BS 225/55 R16 T001 JAP | 4 | 0 | ||
| 33 | 32 | BS 225/55 R17 EP300 THI | 12 | 4 | ||
| 34 | 33 | BS 225/60 R16 EP300 JAP | 10 | 0 | ||
| 35 | 34 | BS 225/60 R16 T001 JAP | 4 | 0 | ||
| 36 | 35 | BS 225/60 R17 D-SPORT JAP | 4 | 4 | ||
| 37 | 36 | BS 225/70 R15C R623 JAP | 8 | 0 | ||
| 38 | 37 | BS 225/70 R16 D687 JAP | 8 | 4 | ||
| 39 | 38 | BS 225/70 R16 H005 THI | 4 | 0 | ||
| 40 | 39 | BS 225/70 R17 D697 THI | 4 | 0 | ||
| 41 | 40 | BS 235/55 R17 ER30 JAP | 4 | 4 | ||
| 42 | 41 | BS 235/55 R17 T005 THI | 4 | 0 | ||
| 43 | 42 | BS 235/55 R18 E031 JAP | 8 | 0 | ||
| 44 | 43 | BS 235/60 R16 T01 JAP | 4 | 0 | ||
| 45 | 44 | BS 235/60 R17 D400 JAP | 8 | 4 | ||
| 46 | 45 | BS 245/40 ZR20 S001 JAP | 4 | 0 | ||
| 47 | 46 | BS 245/45 R19 T005 JAP | 4 | 0 | ||
| 48 | 47 | BS 245/70 R16 D684 THI | 4 | 4 | ||
| 49 | 48 | BS 245/70 R17 D684 JAP | 38 | 0 | ||
| 50 | 49 | BS 245/75 R17 693A JAP | 4 | 0 | ||
| 51 | 50 | BS 255/70 R16 H005 THI | 4 | 4 | ||
| 52 | 51 | BS 265/50 R20 D-SPORT JAP | 4 | 0 | ||
| 53 | 52 | BS 265/60R18 D840 JAP | 12 | 0 | ||
| 54 | 53 | BS 265/70 R18 D684 JAP | 4 | 0 | ||
| 55 | 54 | BS 275/40 R18 S007 JAP | 4 | 0 | ||
| 56 | 55 | BS 275/40 R20 DSPORT JAP | 4 | 0 | ||
| 57 | 56 | BS 275/45 R19 ALENZA JAP | 4 | 0 | ||
| 58 | 57 | BS 275/55 R17 DSPORT JAP | 4 | 0 | ||
| 59 | 58 | BS 275/70 R16 D694 JAP | 4 | 0 | ||
| 60 | 59 | BS 275/70 R16 H005 THI | 4 | 4 | ||
| 61 | 60 | BS 285/60 R18 T01 JAP | 8 | 4 | ||
| 62 | 61 | BS 285/65 R17 R683 JAP | 8 | 4 | ||
| 63 | 62 | BS 315/80 R22.5 G580 JAP | 8 | 0 | ||
| 64 | 63 | BS 315/80 R22.5 R152 JAP | 4 | 0 | ||
| 65 | 64 | BS 315/80 R22.5 R184 JAP | 0 | 1 | ||
| 66 | 65 | BS 385/65 R22.5 R164 JAP | 105 | 121 | ||
| 67 | 66 | BS 445/65 R22.5 R164 JAP | 4 | 0 | ||
| 68 | 67 | BS 650R16 R230 JAP | 16 | 0 | ||
| 69 | 68 | BS 750R16 R230 TCF JAP | 74 | 0 | ||
| 70 | 69 | BS 750R16 VSJ TCF JAP | 112 | 0 | ||
| 71 | 70 | BS 8.25R16 R180 JAP | 0 | 1 | ||
| 72 | 71 | Dayton 315/80R22.5 DS50 THI | 0 | 10 | ||
| 73 | 72 | FS 1200 R24 FSR4000 JAP | 2 | 20 | ||
| 74 | 73 | FS 205/65 R15 TZ700 JAP | 4 | 4 | ||
| 75 | 74 | FS 215/55 R17 TZ700 JAP | 8 | 4 | ||
| 76 | 75 | FS 225/45 R17 TZ700 JAP | 4 | 4 | ||
| 77 | 76 | FS 31X10.50 R15 DEST AT 109S INDO | 4 | 4 | ||
| 78 | 77 | LT285/75 R16 AT001 JAP | 4 | 0 | ||
Bridgestone Sales Report 05May. | ||||||
and when run the macro every time just replace data have already existed for the new sheets have already existed based on match files names are existed in the same folder . every file should create new sheet alone and getting data.
the idea should pull the data from multiple files and put in one file for each sheet instead of using manually. will take more time to do that.
thanks
