Loop copy paste to next column

Similar to: Copy Records where records = "a" at Column B - Syntax Error

Hi All,

This is very similar to something I am working on.

I have uploaded the same file with the required output in sheet 2.

If the cell is X select copy and paste adjacent cell.

But

If cell in next column is X copy and paste adjacent cell (same as before) into next column.

Can anyone help?

thanks Miles

Miles,
If you were looking for a VBA solution, you should have posted it that section.
Your request is not clear.
Filter is a funny method and does not always work. It sometimes copies the header despite you specifay in the range you want to exclude it. It works well with entire row selction.
You have 3 x's in 3 columns. I do not know which row you want to copy once you filter the data in column B. My code copies column F only, but can easily change.
Try it.

Thankyou for coming back to me..

The copied cells do not change, always F.
What F cells are copied and pasted to sheet 2 column C is determined by X's in Sheet 1 column H
What F cells are copied and pasted to sheet 2 column D is determined by X's in Sheet 1 column I
What F cells are copied and pasted to sheet 2 column E is determined by X's in Sheet 1 column J

H...AA
Hope this is clearer

Filter is not probably the ideal code, but try it.

Hi AB33,

I had a play with the sheet and found upon deleting x cells in day 1,2 or 3 it increased the number of cells copied.

Upon delete x cells from column b no cells are copied.

Copying should only be dependent upon X's in day 1,2,3 with column B not existing, nor having influence.

Currently reading into 'filter'

I have 300 days, copy/paste the below many times would not be ideal. Is an incremental loop feasible?

.Range("B4:J" & Lastrow).AutoFilter 8, "x"
.Range("F5:F" & Lastrow).Copy
ms.Cells(2, NC + 1).PasteSpecial xlValues
.AutoFilterMode = False

Thankyou for your help so far AB33, if ever learn what the hell i am doing, i will definitely pay it forward!

Miles,
Yes a loop may be slow, but much more agile and flexible. Please attach a sample with bigger data, may 30-50 days on it.

File is attached.

'Rota input lines' If 1 in column D...Nth copy and paste column C

'yearrota' to column c...nth

Currently does not paste new columns from row 4. Because of DRow = DRow + 1 but this is required... nested loop?

Hope this helps

Miles,
This is completely different request to your original thread.

I could not make the head from the tail or the other way round. You explanation
"'Rota input lines' If 1 in column D...Nth copy and paste column C

'yearrota' to column c...nth"
Not very helpful.
You have many sheets and many codes. I do not know which sheets the input and output.

AB33,

Sorry if I have been unclear

Input sheet: 'Rota input lines'
Output Sheet: 'yearrota'

If a '1' is present in column D (Input sheet)copy cell in column C and paste in column C (output sheet)
If a '1' is present in column E (Input sheet)copy cell in column C and paste in column D (output sheet)
If a '1' is present in column F (Input sheet)copy cell in column C and paste in column E (output sheet)

D,E,F are date columns and go on to ...200 or so.
C,D,E on the output sheet also go to 200 or so


Currently does not paste new cells from row 4 (Run Assign 2 from rotainputlines sheet).

The prior example was the closest that I could find to my problem.


thanks Miles

I have added some dummy data to test the code. It module 11.

AB33,

Thankyou very much for you help. The VBA works perfectly.

I have a further request, I hope I am not pushing my luck!?

I have added a sheet 'rotation'

Within this sheet will be the rotation pattern for each product line.

Rather than select/copy from Sheet: 'rota input lines' column C

It is required that select/copy is from Sheet: 'rotation' column C, select-copy column increases incrementally against the corresponding tick box.

If I have taken enough of your time, thank you again for you help thus far!!!

Miles,
I have a further request, I hope I am not pushing my luck!?

You are not on condition that you should clearly explain your objective. It may be obvious to you as it is your product.
These lines are not clear to me

"Within this sheet will be the rotation pattern for each product line.

Rather than select/copy from Sheet: 'rota input lines' column C

It is required that select/copy is from Sheet: 'rotation' column C, select-copy column increases incrementally against the corresponding tick box"
Your last request was.
Look up for value of 1 in sheet Rota input Lines, in all columns and copy the corresponding rows in to output sheet.
What is the new request? What changes need to be done and which sheet/s?

AB33,

Essentially the idea is to automate a rota.
People need to rotate through tasks in a week as shown in sheet ‘rotation’.
Currently the VBA takes values from sheet ‘rota input lines’ :column C dependant on whether the box is ticked and puts them in sheet: year rota.

The problem is currently an employee could by chance be doing the same job in the morning and afternoon, which is bad.
The requirement is for the staff to rotate through the tasks, so as to ensure they get variety.
The easiest way I thought to do this was to create another sheet ‘rotation’.

Instead of taking the value from 'rota input lines' column C it would be taken from 'rotation', this would ensure employees would not have the same task as the previous day.

Hope this Helps... Should I annotate the excel file ?

grabbing some lunch

In short,
You want to copy the data in sheet rotation to sheet YearRota. Is this right? If so, on what condition/ what are the criteria?
You have date, and days in rows of 1-3 in sheet rotation, so are you trying to match these heading with rows 1-3 in sheet yearrota and if the match you want to copy the rows from sheet rotation in to sheet yearrota?
For e.g in sheet rotation column C, you have 01 April 2013, Monday. You have the same heading in sheet yearrota in columns C and D, so both heading match.
Just need more clarity.

Went with vlookup, allows for a simple rotation.

Back to the original problem, I have tested with all tick boxes and it does not work.

Is this because of the number of tick boxes? I suspect not, as the vba is only interested in 1's. N

Nothing has changed apart from the number of 1's and 0's in sheet ...

After this I will post as SOLVED! I promise.

Back to the original problem, I have tested with all tick boxes and it does not work.

On the attached, there is no 1 in all rows in sheet Rota input Lines and returns 0.

The condition is if a cell value is 1.

In short,
You want to copy the data in sheet rotation to sheet YearRota. Is this right? If so, on what condition/ what are the criteria? - YES

I understand this must be frustrating!

IF column cell has a tick
MATCH from column 3 (C) and MATCH column number
SELECT cell from 'rotation'
PASTE cell into 'Yearrota"
move to next cell
then move to next column.

As mentioned i used a vlookup :
VLOOKUP(YearRota!C5,'Rotation formula'!$A$8:$TN$210,2,FALSE))

and created another yearota2 to present the results.

AB33 thankyou again, you generosity with your time is greatly appreciated.

Miles

Miles,
I am now confused. I do not which one is which and which one is working/ no working?
You are going left and right and I am losing to track you.
I thought your yesterday's request was dealt with. This morning you came up with a third request with in 2 days.
I asked you for clarity and you said you have used Vlook up formula, so no need for a code. You then went back to the original request and now back to second request.
It is Friday and the sun is out.

Your right! I am done in an hour..

I sent the message about the third problem which is the same as the first before i realized you responded to the second, timing issue.


Enjoy the weekend AB33.

Will respond on Monday. In the mean time, food for thought for you.

There are not ticker boxes in sheet rotation. In fact the only sheet which has a tick is input data.

Hi AB33,

How to I mark this as solved by you!

I have another problem, it is explained better i think?

http://www.excelforum.com/excel-gene...62#post3387662

Please take a look!