Macro Pasting Values Instead of Formulas

Hello again! I have a macro that correctly identifies regions in the "raw data" file on Sheet1 and extracts the data to separate worksheets based on the region value (e.g., "Central Region," "Eastern Region"). However, there are formulas that are in Sheet1 that are for some reason being pasted as values in the regional worksheets. Can you help me identify why this is occurring, or more importantly, how to prevent it from occurring? Here is the macro:

Please Login or Register  to view this content.

Hi, Jerry,

Thanks for the suggestion! I tried the code you proposed, but unfortunately received the following error message: "Run-time error '1004': Unable to get the Transpose property of the WorksheetFunction class." The error was related to this line:

Please Login or Register  to view this content.

Thanks for your quick response, Jerry. I will have to test it a little later today. If it works, I'll definitely come back and mark this case "Solved."

Here is a sample file. Thanks for taking a look at it!
TEST.xlsx

Well, the regions are more or less static, but there may be times when any given region out of seven may not have data for a given date, and thus would not appear. In that case, I suspect the array would not work. I have a workaround that is working where I just redo the formulas on each sheet, and that can definitely get me by as the report is produced correctly. I was just looking for a more efficient way of running the code so that it wasn't necessary on each sheet.

Hi, John! Did you test the file I uploaded with Jerry's original code or the second code he provided? I'm sure the second code would work, but only if all regions in the array are always present in the data.

Hi, Rory! Your code worked perfectly! The formulas were all retained on each sheet.

Thank you and to everyone that responded!

Bryan

Jerry, thanks for the basic code, and Rory, thanks for the tweak to Jerry's code! I do feel rather stupid, however. When I originally posted, I took out the code that referenced additional macros that run on each worksheet after they're extracted. I tried placing them back in the new code, but I'm getting errors which I'm sure are due to my placing them in the wrong place. This is what happens when you're new to VBA and can't always follow the code logic.

I thought they should go between the WSNew.COLUMNS.AutoFit and the Next rngCell lines in the code, but that didn't work. Can you help me understand where they REALLY should go?

Thanks!

Rory, thanks again for the quick response! It turns out the problem was with the macro that was doing conditional formatting, not where I placed the macros or even adding the WSNew.Activate code. It seems that there was a lot of junk conditional formatting that had been saved in the worksheet and I think it was just running out of room to add more. I discovered that if that conditional formatting is just placed on the first sheet to apply to a range of cells, it doesn't have to be in the macro code later. Thanks again for all of your kind help!

Bryan