Good afternoon Jonathan,
First of all thank you for your feedback.
Actually, I think it is a bit more complex.
Sheet 1: is my datasheet which I want to refresh every week with new data (just by a manually performed copy-paste action); the column headers (for examle two or more columns: "Name 1", "Name 2" etc.) will not change (only the data below all column header names will be renewed/refreshed). BUT column "Name 1" (for example) can be changed and be the fourth column when counting from left to right, and in the following week it can be the fifth column counting from left to right. So wherever column "Name 1" is positioned, i need a kind of Hlookup function in order to locate the "Name 1" column.
When I have found the "Name 1" column, I then want to automatically copy all data which is contained in the cells of that (respective column "Name 1" and below the column name) and paste it in another sheet (in this case Sheet 2): this automatically copy and paste part is my problem.
Sheet 2: must be my overview sheet of all columns having (copy pasted) data. And the columns will always be located at the same pre-defined fixed position. So during every refreshment of data, the columns in sheet 2 will not change from position.
So actually, when I would have four different Datasheets (four different types of "Sheet 1") then "Sheet 2" must be the sheet where all data from those four sheets are being gathered and structured in the correct columns (column names in sheet 2 will be recognizable in the four datasheets, but can be located at different column positions in the four datasheets).
Thus, I first need to fill my four datasheets with data and then search the data by using column header names, and then copy>paste (transfer)(automatical action preferred) the data which is below the respective column header names, to Sheet 2 in the correct column.
And then I will use sheet 2 in order to create a pivot table.
Sorry for the long text.