Hello everyone,
Pretty much new here and in VBA. I am working on a internship project that just consumes too much of my time (please dont think I am trying to escape the task which I don't I am just looking for a better and efficient way of doing this monthly routine).
On a monthly basis I get this report that has information across columns . Set up looks like this
<colgroup><col><col span="8"></colgroup><tbody>
</tbody>
I need to prepare the information into another sheet lets call it "dataset" by copying columns A to G twelve times in a column A so that I can copy individual columns J to U data under one column. I will need to do these for all X to AD, AS to AY and BN to BT followed by the JAN- DEC data. The number of rows varies each month but they are the same across the columns each month.
so for example, for simplicity we have 3 rows of information across all columns, I will copy all 3 rows for A to G and paste it in A1 from row 1 to 12 then 13 to 24 then 25 to 36. After this I will copy column J, then the next column to the last cell with information from J till i get to U. Rinse and repeat for the rest of the columns. Its okay when I deal with few hundred rows but becomes tedious when the rows turns in thousands. I am hoping for some assistance where I can incorporate a VBA code to automate this. Any help will be great.
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>
Thank you in advance!
EAS
Pretty much new here and in VBA. I am working on a internship project that just consumes too much of my time (please dont think I am trying to escape the task which I don't I am just looking for a better and efficient way of doing this monthly routine).
On a monthly basis I get this report that has information across columns . Set up looks like this
| Header | Account information | JAN - DEC | Account information | JAN - DEC | Account information | JAN - DEC | Account information | JAN - DEC |
| Columns | A to G | J to U | X to AD | AE to AP | AS to AY | AZ to BK | BN to BT | BU to CF |
| Number of Columns | 7 columns | 12 Columns | 7 columns | 12 Columns | 7 columns | 12 Columns | 7 columns | 12 Columns |
<colgroup><col><col span="8"></colgroup><tbody>
</tbody>
I need to prepare the information into another sheet lets call it "dataset" by copying columns A to G twelve times in a column A so that I can copy individual columns J to U data under one column. I will need to do these for all X to AD, AS to AY and BN to BT followed by the JAN- DEC data. The number of rows varies each month but they are the same across the columns each month.
so for example, for simplicity we have 3 rows of information across all columns, I will copy all 3 rows for A to G and paste it in A1 from row 1 to 12 then 13 to 24 then 25 to 36. After this I will copy column J, then the next column to the last cell with information from J till i get to U. Rinse and repeat for the rest of the columns. Its okay when I deal with few hundred rows but becomes tedious when the rows turns in thousands. I am hoping for some assistance where I can incorporate a VBA code to automate this. Any help will be great.
| BEFORE | |||||||||
| Account information | Account information | Account information | Account information | Account information | Account information | Account information | JAN | FEB | MAR |
| A | A | A | A | A | A | A | 1 | 2 | 3 |
| B | B | B | B | B | B | B | 1 | 2 | 3 |
| C | C | C | C | C | C | C | 1 | 2 | 3 |
| AFTER | |||||||||
| Account information | Account information | Account information | Account information | Account information | Account information | Account information | PERIOD | DATA | |
| A | A | A | A | A | A | A | JAN | 1 | |
| B | B | B | B | B | B | B | JAN | 1 | |
| C | C | C | C | C | C | C | JAN | 1 | |
| A | A | A | A | A | A | A | FEB | 2 | |
| B | B | B | B | B | B | B | FEB | 2 | |
| C | C | C | C | C | C | C | FEB | 2 | |
| A | A | A | A | A | A | A | FEB | 3 | |
| B | B | B | B | B | B | B | FEB | 3 | |
| C | C | C | C | C | C | C | FEB | 3 |
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>
Thank you in advance!
EAS
