Need VBA code to copy/paste data from one sheet to another with multiple criteria and complex structure

[Amir Wisal]

Hi VBA Ninjas,

Hope you're all doing great.

i am working on a very complex file and it would be great if i can automate some part of the process. I know this is a lengthy project and it might quite some time but I would really appreciate if you guys help me on this.

Available Information:

1. column "T" of GL tab (which is actually trail balance) shows closing balance and during the period (which is one month in this case) in column R and S respectively.
2. C1239 report shows breakup of the activity along with other details like opening and closing balance.
3. My spreadsheet has a lot of tabs, which can be changed depending on the number of accounts mentioned in GL report.
4. My objective is as follows - For reference, I will be referring to "935-2 Prepayments 1" tab.
   1. I need a VBA code which will go to the "935-2 Prepayments 1" tab first, find the data format which is in column A (which will be "B22" in this case as my main data starts from there, move 3 columns to the right to the period column, search which is the last entered period. This is important as I want to bring P12 data only from C1239.
   2. After assessing the period which is P11 in my case, I want it to add 1 to it as I want P12 data to be copied here from C1239 report. After that, I want VBA to go to the C1239 tab, filter on the account and sub account column (which is column G and H) as relevant to the tab name (for this step, I have added the account number in column C and sub account number in column D but their position is different, in some tabs in will be C10, C16 or maybe different cell number).
   3. After filtering the data based in the criteria as above (period, account and sub account), I want it to insert the number of rows of data filtered below the last entered data. In this particular case (935-2), I have only one row of data which can be different in each case. I want the code to insert the number of rows of data filtered in C1239 report and then copy the data from C1239 report and paste it below the previous period data. Please note that I don't want the the whole row of data I only want relevant columns whose headers are mention in my working spreadsheet (935-2).
   4. Important Points:

1. I have multiple tabs and I want this process for most of them however I want to exclude some sheets (which are highlighted as yellow on the GL). Is it possible? If yes, can the code have a popup window where I can tell him the accounts and subaccounts for which I don't want to run the above procedure.
2. Inserting new rows in the working spreadsheet should be based on the number of entries in a particular period in focus. So if code has filtered out 12 items for a given account, it should insert 12 rows in the sheets where the data is to be copied and then paste the data.
3. Macro should run in loop, as I want it to look for all the accounts except some and perform the above procedure.
4. Required column form C1239 is highlighted as green

Working file can be accessed from the link below. please have a look.

Sample.xlsx

GL291 COMPANY,Name,Currency Code,CURRENCY-NAME,REPORT-TYPE-LIT-XLT,PERIOD,ALPHA-DATE,FISCAL-YEAR,VAR-LEVELS,ACCT-UNIT,VALUE-NAME,PERSON-RESP,ACCOUNT,DASH,SUB-ACCT,ACCOUNT-DESC, ACTIVITY-BB , ACTIVITY-DB , ACTIVITY-CR , ACTIVITY-CB 0,0,0,0,0,12,0,0,0,0,0,0,930,-,0,Work In Progress, 457,506,272.45

docs.google.com

if you need additional information, let me know

The same query is also posted on other forums. link is Need VBA code to copy/paste data from one sheet to another with multiple criteria and complex structure

Many Thanks,