Multiple conditional Copy and paste from 1 workbook to 2nd workbook with various sheets.

[RAKESH230583]

Dear All,

I am working on below requirement and am finding it very difficult as it have various scenario of Copy and paste from 1 excel workbook to 2 excel workbook.

Workbook 1:
This workbook gets auto generated from system and has 6 different sheets in it. In all sheets there are huge rows of data.

Sheet 1 To 5 : From Row 1 to Row 7 : It has information about the data, whereas the actual information starts from column B8 till AH8 (Headers) (For sheet 1 to 4 ) whereas for sheet 5 its start from B8 till AJ8 (Headers) - Maximum filled row under Sheet 1 to 5 is 95,000 filled rows under each sheets.

For all above 5 Sheets, Value under cell B8 is same that is "Region".

Sheet 6: From Row 1 to Row 8 : It has information about the data, whereas the actual information starts from column B9 till AF8, With maximum filled row as ~200,000 rows.

Now, under sheet 6, there are multiple ranges - For example 1st one is "B9:AF65000", 2nd one is "B65035:AF95200", 3rd one is "B95210:AF125000"and so on... But, good part is that under all range column B have same value "ABC country" under header "Product".

Workbook 2: This is the workbook wherein i had given all commands based on the requirements.
Now under this workbook, I had created 6 different sheets, Within the line of Workbook 1.

Requirement:

1. Sub to 1st check, whether Worksheet1 "Data report_Daily.xlsx" is active or not - If not then, must give the msgbox to the user that "Data report_Daily.xlsx report is not active, Kindly open & then run the command" and exit sub. If Active then run the sub.
2. Sub to copy and paste the Data as per above Rage till it last filled rows and then paste it to Workbook 2. (For example Copy data from Workbook 1, Sheet 1, Range (B8:AH8) xldown till it last filled row and paste special in Workbook 2, Sheet 1 in Range A1. (Copy data from Workbook 1, Sheet 1 & Paste special in workbook 2, Sheet 1, A1. Copy data from Workbook 1, Sheet 2 & Paste in workbook 2, Sheet 2, A1 & So on all 6 Sheets.)
3. Post completing the paste special - it converts the ranges into table under all 6 sheets under worksheet 2.

Thanks for your time in advance & please comments if anything is not clear.