Hi, I was wondering if the experts in here could help me out. I need to write a macro for what is essentially a data transfer task.
I'm supposed to write a script to copy and paste data from individual sheets to a consolidated sheet (these are all in the same workbook). However, there are some conditions which have complicated this for me and I'm not sure how to move forward (please see the Excel file attached):
(Point 1) I'm only supposed to transfer data from any sheet that contains the words "ABC Report" to the "Consolidated" sheet. The other sheets are supposed to be ignored by the script.
(Point 2) Within each of the "ABC Report" sheets. I'm not supposed to transfer all the data. I'm only supposed to transfer data from specific Account IDs, in this case they would be "Account 1" and "Account 3".
(Point 3) Within the specific account, I'm only supposed to copy certain cells into the "Consolidated" sheet. These cells are always in between "Currency" and "Total". For example, in the worksheet "ABC Report 1", I'm only supposed to copy cells A5:B6 into the "Consolidated" sheet. The data is always structured such that the cells are in between "Currency" and "Total", and there are always blank cells around "Currency" and "Total".
(Point 4) After copying the data, the cells need to be separated and pasted into specific new columns in the "Consolidated" sheet. I have done an example for cells A5:B6 from "ABC Report 1":
A5 from "ABC Report 1" has been pasted into G2 in "Consolidated",
B5 from "ABC Report 1" has been pasted into K2 in "Consolidated",
A6 from "ABC Report 1" has been pasted into G3 in "Consolidated",
B6 from "ABC Report 1" has been pasted into K3 in "Consolidated",
The template I have attached is a simplified version of my actual workbook which has thousands of rows and many tabs. I'm hoping that the simplified file presents the essence of the tasks that I need to accomplish. In particular, I need a macro that
(i) Only looks at sheets that have the string "ABC Report" in the sheet name
(ii) Only looks at specific account IDs. (for my actual report, I already have the list of these IDs and I'm thinking that I can create some sort of array for this?)
(iii) For the specific account IDs, only copies the cells in between "Currency" and "Total" (I'm thinking that some sort of Offset from the account ID and CurrentRegion can be used, since there are always blank cells around the cells to be copied)
(iv) Rearranges the cells when pasting them into "Consolidated".
I'm hoping that once I have a basic general approach and macro I can adapt it to my actual sheet. I can't share the actual sheet because it contains sensitive data.
In summary, I'm hoping my request is clear, and I would be eternally grateful to anyone who can help me out. Thank you.
Bookmarks