PDA

View Full Version : Solved: Copy and Paste On Formula Result



hobbiton73
02-15-2013, 09:50 AM
Hi, I wonder someone may be able to help me please.

@p45cal from this forum, helped me in putting the solution below together. The macro copies rows from one sheet to another if a cell within the given row matches a specific formula result.

Sub SpareResource()
Set xx = Sheets("Resource Summary").Range("A1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1")
ARow = 5
Set cll = Sheets("Resource Summary").Cells(ARow, 1)
Do While Len(cll.Value) > 0
Rw = cll.Row
If Evaluate("SUM(--((IF(MOD(COLUMN($D$1:$Z$1)/2,1)=0,'Resource Summary'!$D$" & Rw & ":$Z$" & Rw & "))<(IF(MOD(COLUMN($C$1:$Y$1)/2,1)<>0,'Resource Summary'!$C$" & Rw & ":$Y$" & Rw & ")*0.85)))>0") Then
With Sheets("Staff With Spare Resource")
destrw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
DestColm = 1
For Each celle In xx.Offset(Rw - 1).Cells
celle.Copy .Cells(destrw, DestColm)
DestColm = DestColm + 2
Next celle
End With
End If
Set cll = cll.Offset(1)
Loop
End Sub
I'd like to tweak this a little , but having spent all day trying to do so, I'm not sure how to find a resolution.

There are two chnages, which, if at all possibe I'd like to make.

In addition to the current copy and paste range I'd like to also include column B, and rather than pasting, I'd like, if at all possible, to change this to 'paste special'.

I'm not particularly good with VBA, but as I said, I've been trying to solve this all day without any success.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may achieve this.

Many thanks and kind regards

Chris

p45cal
02-15-2013, 01:26 PM
Could you confirm that you'd like to include column B FROM the Resource Summary sheet.
Where's its destination?
Are all the other columns sources/destinations the same?
Paste-special.. what? (Values?)
Do you want to paste-special all the cells copied over or just column B?

hobbiton73
02-16-2013, 06:40 AM
Hi, thank you very much for taking the time to reply to my post.

I can confirm that I would like to copy from column B on the 'Resource Summary' sheet to column B on the 'Destination' sheet.

All other column source and destinations are as per the original code.

If possible, I would like to 'Paste Special' all the values from the 'Resource Summary' sheet to the 'Destination' sheet.

I hope this helps.

Many thanks and kind regards

Chris

p45cal
02-16-2013, 07:41 AM
untested, try:Sub SpareResource()
Set xx = Sheets("Resource Summary").Range("A1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1")
ARow = 5
Set cll = Sheets("Resource Summary").Cells(ARow, 1)
Do While Len(cll.Value) > 0
rw = cll.Row
If Evaluate("SUM(--((IF(MOD(COLUMN($D$1:$Z$1)/2,1)=0,'Resource Summary'!$D$" & rw & ":$Z$" & rw & "))<(IF(MOD(COLUMN($C$1:$Y$1)/2,1)<>0,'Resource Summary'!$C$" & rw & ":$Y$" & rw & ")*0.85)))>0") Then
With Sheets("Staff With Spare Resource")
destrw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
DestColm = 1
For Each celle In xx.Offset(rw - 1).Cells
.Cells(destrw, DestColm).Value = celle.Value
DestColm = DestColm + 2
Next celle
.Cells(destrw, "B").Value = Sheets("Resource Summary").Range("B" & celle.Row).Value
End With
End If
Set cll = cll.Offset(1)
Loop
End Sub

hobbiton73
02-16-2013, 08:11 AM
Hi @p45cal, thank you very much for coming back to me so quickly with this.

I've tested the code you kindly posted, but unfortunately I recieve the following error:

'Run-time error '424': Object required' and this is the line which is highlighted in the debug screen:

.Cells(destrw, "B").Value = Sheets("Resource Summary").Range("B" & celle.Row).Value

If I block this line out, the code runs to the end and correctly 'Paste Specials' values from all but column B.

I hope this helps.

Many thanks and kind regards

Chris

p45cal
02-16-2013, 01:11 PM
try replacing celle.row on that line to just rw, but check it's the right row that the column B data is coming from/to.

hobbiton73
02-17-2013, 06:35 AM
Hi @p45cal, thank you for coming back to me with this.

I've made the changes you suggested, so the line in question now looks like:

.Cells(destrw, "B").Value = Sheets("Resource Summary").Range("B" & rw.Row).Value

But, unfortunately, I'm still receiving the same error message.

Many thanks and kind regards

Chris

p45cal
02-17-2013, 07:20 AM
try replacing celle.row on that line to just rw, but check it's the right row that the column B data is coming from/to.
I said replace it with rw, not rw.row.

hobbiton73
02-17-2013, 07:47 AM
Hi @p45cal,my apologies, I misunderstood what you had said.

I've tried the suggestion you made, and this now works great! thank you very much for all your time and trouble.

On another note, you're clearly quite an expert on VBA. I'm very keen to learn more and gain the confidence to start writing my own procedures.

Could you tell me please, are there any online resources that you would recommend I start to look at?

Many thanks and kind regards

Chris

p45cal
02-17-2013, 02:09 PM
Loads.. Google for excel vba tutorial.
Also
Resources
http://www.vbaexpress.com/resources.php
Mr Excel (Bill Jelen) is giving away his new book as a free ebook download!!! - VBA Express Forum
http://www.vbaexpress.com/forum/showthread.php?t=24408
Bill Jelen Is Giving Away His Book For Free
http://www.mrexcel.com/marketwatch.html

hobbiton73
02-18-2013, 10:41 AM
Hi @p45cal, thank you very much for this.

I've started to look at these.

Kind regards

Chris