Xen
Member
Hello,
I have this workbook which contains six sheets with data where dates are from column B to column AE, then we have up to hounder rows of information for charts (sometimes new rows are inserted in between or in the end of existing ones). Then, it has few dozens of charts on different sheets, each having 3-6 series. As usually, people who made this workbook never heard about dynamic ranges. And updating all the series manually or defining and assigning names will take a lot of time from this point.
And logically, all I have to do is replace $AE with $AF across the workbook.
But how do I do that without wasting too much time on what is purely busywork?
I've attached sample workbook. Which is not even close to that nightmare workbook that I have in reality, but basic principle is the same.
I'm not good at VBA at all. at one point, I even tried to unpack xlsx and do a find and replace job, but then I saw that each chart has it's own xml file, which is still some manual work considering number of charts in workbook. And that manual work, as I can suppose, can be avoided somehow?
I have this workbook which contains six sheets with data where dates are from column B to column AE, then we have up to hounder rows of information for charts (sometimes new rows are inserted in between or in the end of existing ones). Then, it has few dozens of charts on different sheets, each having 3-6 series. As usually, people who made this workbook never heard about dynamic ranges. And updating all the series manually or defining and assigning names will take a lot of time from this point.
And logically, all I have to do is replace $AE with $AF across the workbook.
But how do I do that without wasting too much time on what is purely busywork?
I've attached sample workbook. Which is not even close to that nightmare workbook that I have in reality, but basic principle is the same.
I'm not good at VBA at all. at one point, I even tried to unpack xlsx and do a find and replace job, but then I saw that each chart has it's own xml file, which is still some manual work considering number of charts in workbook. And that manual work, as I can suppose, can be avoided somehow?
