Paste Data from One Excel Into First Empty Column

[Drivingman]

HI All

This is going to be long question and I will try and break down into many steps. I know what I want, just lacking a little (or lot) of knowledge to do it.

1 - I need to paste data from one sheet into my results file daily. The data that is copied is a csv report that has dynamic name depending on date it is ran. So thought would be easier if I just had the data copied already.
This data is pasted into sheet "Daily Import" into the next empty column. Currently one data dump takes from column A to R, so the next days data starts at S, and so on.
So what VBA would I need in my file to do this automatically.

2 - I have created the formulas for my first days pull down.


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thurs[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[/TR]
[TR]
[TD]Day 0[/TD]
[TD]Open[/TD]
[TD]=COUNTIFS('Daily Import'!I:I,C$3,'Daily Import'!O:O,D$3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stage 1 Review[/TD]
[TD]Closed[/TD]
[TD]=COUNTIFS('Daily Import'!I:I,C$4,'Daily Import'!O:O,D$3)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stage 2 Review[/TD]
[TD]Error[/TD]
[TD]=COUNTIFS('Daily Import'!I:I,C$6,'Daily Import'!O:O,D$3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stage 3 Review[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stage 4 Review[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Post Stage 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formulas I have in Monday are shown above. These are without any other posts, and I need these to change ready for the second week of posts. But first, I need the formulas in for Tuesday - Saturday.
I could manually put these in, but after one week pasting data I will be needing to redo them all.
So how will I get dynamic formulas based on how many pastes that step 1 have done.

Week 1 Mon - number of pastes - 1 (so formulas as above)
Week 1 Tues - number of pastes - 2 (so all formulas within Daily Import need moving 17 columns)
Week 1 Wed - 3 pastes so formulas moved on another 17 columns

Week 2 - think this will be the nightmare, and may need some very creative thinking.
Thinking outside box here and maybe helping - in cell A1 I have week number 1,2,3 and so on. And then the formula could be based on that cell, and a second cell detailing the day of week that we are uploading?

3 - Once a full weeks worth of data is completed then need something (VBA) to pull the data into similar row sheet.....
But that can be done once I have couple of weeks data. Want 1 & 2 sorted first.


Do not worry about column D that much. This is for what the formula is asking for.
Hope this makes sense.
TYIA for any help you can give me.

 

[Drivingman]

Hi ALl

Just a little note on this one.
I have sorted out the paste part now.

So now just to change the formulas of the table.

How could I setup a formula to look for specific columns based on a formula?

The column numbers will be (9+(cell O1-1)x17) changing each time.
Will it be INDEX? I cant use match as this will repeat several times in each daily post.

 

[Drivingman]

I sorted the issue with dynamic range.
My formula needed a new table to take some of the calculation then I sorted it with INDEX.

=COUNTIFS(INDEX('Daily Import'!$1:$1048576,,E$3),$I$14,INDEX('Daily Import'!1:$1048576,,E$4),$J$14)

E3 and E4 did the dynamic calculation as every time I tried it together then got #VALUE error.