Hi all,
i have a scenario here. I have attached a sample file.
The background of this spread:
I have 3 worksheets, named Data1, Data2 & Pivot. I have actually created macros to format my raw data to derive at Data1 & Data2. In this file, the macros are not attached. Let us just assume the data is formatted and ready. Now, I need to analyse the consolidated data from Data1 & Data2, hence i would need to manually cut and paste the data and paste it at Worksheet("Pivot"), and allow the pivot tool to create a pivot table for me.
Now...
I created a Macro to actually select the sensitive range in Data1 and Data2, bearing in mind that my data every week for Data1 & Data2 keeps on changing. The macro i created doesnt work, as nothing get pasted to my Worksheet ("Pivot"). The Macro named UpdateData is created in this worksheet, you can take a look at it.
what needs to get done?
1. Need to get excel to recognise the data range (for Data1 & Data2) and copy and paste it to Pivot worksheet. The function must be dynamic in the sense, it is clever enough to detect the ever-changing range every week.
For example, my data range for Data1 for week1 is from A1:C12, and data range for Data1 for week2 is from A1: C20. Hence, excel must be able to recognise the changing range of data. That applies the same for Data2 as well.
2. Notice that there are 2 worksheet that i would get my data from, Data1 & Data2.
After Data1's data is being pasted onto Pivot worksheet, in this case, refer to the attachment, WorkSheet "Pivot", Excel should be able to recognise and identify A14 as the cell for Data2 to be pasted. Bearing in mind that the data changes everyweek, excel must be clever enough to detect that cell for the next data to be pasted.
How do i go about doing it?
I hope this is concise enough.
Hear from you guys soon. Thank you for taking time off to read this. = )
Cheers...