Copy paste Some selected Cells of a row on same sheet based on value [SOLVED]

[dev.sutradhar1987]

I have a Sheet where i have column from (A - BM). I have a Dropdown in Colomn "F". When i will select "Reschedule" It will Copy certain Cells of the Row which are (A,E,f,g,j,l) And paste in in the next empty row.


I know how to copy paste it but i am unable to understand how to when i need on certain cells to be pasted not the entire.


Please someone help.

 

[jeffreyweir]

Hi. Which cells does it copy? You just say 'certain cells'. Is it copying the whole columns (A,E,f,g,j,l) and adding this at the bottom of (A,E,f,g,j,l)? Or is there some cirterai in which you copy only particular rows from (A,E,f,g,j,l)?


Also, what version of Excel are you using. Copying data to the bottom of a range is easiest if your data is is an Excel Table (introduced in Excel 2007).


Edit: Ahh, I see that the title of your post says 'Based on a value'. What value?

 

[dev.sutradhar1987]

Suppose there is a row (A8 - BM8).In the Row a cell D8, there is a Dropdown where if "Rescheduled" is selected then A8,E8,F8,G8,J8,L8 will be copied and pasted on the same sheet in the next empty row.


i am using excel 2007.


The vale is "Rescheduled"


I gave a example for row (Row8).This should happen in the entire sheet.The sheet name is "Ongoing Process".

 

[Marc L]

Hi !


Code to insert in the class module of the worksheet :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If .Column = 4 And .Value = "Rescheduled" Then
R& = Me.UsedRange(Me.UsedRange.Count)(2).Row
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each C In [{1,5,6,7,10,12}]:  Cells(R, C) = Cells(.Row, C):  Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End If
End With
End Sub

[/pre]

Workbook must be saved in format .xlsb or .xlsm !


Could be the .xls format for sharing with previous Excel version …

 

[SirJB7]

This topic is the same as last question of this, just a week of difference:

http://chandoo.org/forums/topic/macro-to-copy-and-paste-based-on-certain-condition


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the main green sticky post at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker.

If you do cross post, please put that in your post.

Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

 

[dev.sutradhar1987]

Hello Sir JB7,


I apologize for the Cross Posting i have done.Fro next time onwards i will keep that in mind in post.


And thanks for letting me know some facts.


And i will keep these things in mind.

 

[dev.sutradhar1987]

Hi, Marc L,


I tried the code it is working fine.


Thank you very mucg for the code but i just need one clarification on this that is I want is when it is finding the next empty row it should do it on the basis of Column "A" and should find the row without any text.Other wise it is going way down as many cell have fromulaes in it.


"Doing a Ctrl+up command to find first cell w/O text"

 

[Marc L]

Hi,


exactly what my code does ‼   Just read the VBA Help of the  UsedRange  property …


And basis of Column "A" does not mean that others are empty on the same line !


Edit :  I just understand the formula problem,

          so be patient 'cause it's the same problem with Ctrl + Up if there is a formula in column A …

 

[Marc L]

If a formula could be in column A :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If .Column = 4 And .Value = "Rescheduled" Then
R& = Me.UsedRange(Me.UsedRange.Count).Row
R = R - (R < Me.Rows.Count)
Do:  B = Cells(R - 1, 1).Text = "":  R = R + B:  Loop While B And R > 1
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each C In [{1,5,6,7,10,12}]:  Cells(R, C) = Cells(.Row, C):  Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End If
End With
End Sub

 

If there is never a formula in column A :


Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If .Column = 4 And .Value = "Rescheduled" Then
R& = Cells(Me.Rows.Count, 1).End(xlUp)(2).Row
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each C In [{1,5,6,7,10,12}]:  Cells(R, C) = Cells(.Row, C):  Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End If
End With
End Sub

[/pre]
 

Enjoy ‼

 

[dev.sutradhar1987]

Hello Marc L,


Thanks a Ton..


Wroking absolutely Fine. Cheers

 

[Marc L]

Thought for the day (applicable each day) :


Question well formulated, solution guaranteed !