First, I want to say sorry if this has been extensively covered, but I could not find a post to satisfy what I need my spreadsheet to do.

So I have a spreadsheet that I did not build that pulls tons of data from a simulation and throws it into a new worksheet. I can choose all sorts of data to be extracted, but I am interested in pulling Mass Flow, Molar Flow, and Volumetric Flow from a varying number of streams. For example, in this simulation, I will pull Component Mass Flow, Component Molar Flow, and Component Volumetric Flow data from streams 1, 2, 18, 19, 20, and 36. The stream numbers will change every time I use the spreadsheet and there will be more or fewer streams each time. So this has been done and is now on the new worksheet in a very unusable format.

The stream numbers go across on row 3 starting at column E. There are 6 streams in this case, but can be as few as 2 or as many as 100. This changes in each case. Down Column C runs the different components that make up each stream: Methane, Ethane, i-Butane, etc. There are a total of 55 of these components. This number does not ever change. When the data is pulled into the spreadsheet, the Component Mass first pulls into the spreadsheet, the Molar flow data below that, and the volumetric flow data below that. What I want to be able to do is copy the Mass flow data (E5:J59) to a new worksheet in cells D6:I60, the Molar Flow Data (E60:J114) to J6:O60, and the Volumetric data from E115:J169 to P4:U60. This would be simple for me if the number of streams were not changing, requiring the selected range to be changed every single time. I currently have cell B2 setup to count the number of streams on the setup worksheet to be used in the VBA code.

Any help would be greatly appreciated!

Copy of HSR 1.6 (for HYSYS 2004).xlsm