Copy range to multiple sheets based on criteria with lookup

Hello all,

I have an API call that pulls data relating to 34 individual sites. Each site has a varying number of assets within it, each with a unique identifier.

I am trying to write a macro that will copy and paste the data for specific sites into their own individual worksheet within the file. The basic concept of this I am familiar with and am sure I could usually manage something using auto filter.

The problem I am hitting however is that the API call pulls out a different unique asset identifier compared to what I use for reporting.

What I mean by this is that each asset has a unique name for example ACH_WTG01, ACH_WTG02 etc, but they also have a unique number assigned to them for storing in the database that I am pulling the data from.

So ACH_WTG01 also has unique number reference 48 and ACH_WTG02 has number reference 49 and so on.

I would usually use a VLookup to display the data with the more easily understandable asset name rather than the number reference, but I am struggling to figure out how to do this within a macro.


I have attached a demo copy of the sheet I will be using that shows the raw data as it comes out after I run the API call, along with the sheet that I would use for the VLookup reference and a couple of the sheets I want to copy the data into.

My questions are:

1. Is it possible using a Macro to lookup the Asset ID and then copy and paste the site specific data from that range in Raw Data into the individual site sheets but to also display the Asset Name rather than the ID number?

2. Can this be done using the Sheet name which is the Site Abbreviation, or would I need to have a reference cell within each of the individual site sheets?

Apologies in advance if my explanation is not very clear. I have very limited experience using macros so I am not sure if my way of explaining what I want to achieve is understandable or if at all possible.
I am very keen to learn however and any guidance you fine folk could offer would be very much appreciated.

Thanks in advance!

Hello again.

I've just realised I can merge tables into my API Query so have managed to bypass the need to lookup or replace any values in the macro script.

All I need to do now is script out the macro to copy and paste the data into the relevant sheets.