Solved: Copy and Paste Row if Cell Has Value

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using a spreadsheet called 'Resource Summary 12-13' which profiles staff resource across the year, and by using a combination of formulas and conditional formatting, any anomalies are highlighted.

What I'm trying to do is copy rows which has cells which meet a specific condition and paste them into another sheet. To be mores specific.


Look in the sheet 'Resource Summary 12-13' and check that there is a value in column A starting at row 5 until it reaches a blank cell,
Upon a value being found I'd like to be able to have a process which then reads across that row and searches in columns D, F, H, J, L, N, P, R, T, V, X, and Z, to check that the following is true:


Column D is less than Column C * 85%
Column F is less than Column E * 85%
Column H is less than Column G * 85%
Column J is less than Column I * 85%
Column L is less than Column K * 85%
Column N is less than Column M * 85%
Column P is less than Column O * 85%
Column R is less than Column Q * 85%
Column T is less than Column S * 85%
Column V is less than Column U * 85%
Column X is less than Column W * 85%
Column Z is less than Column Y * 85%

For any rows that meet the above criteria, I'd like to copy the cells in columns A, D, F, H , J, L, N, P, R, T, V, X, Z and paste them into the 'Forecasts' sheet in columns A, C, E, G, I, K, M, O, Q, S, U, W and Y.

I really just wondered whether someone may be able to take a look at this and offer some guidance on how I may go about achieving this.

Many thanks and regards

[p45cal]

try:
Sub blah()
Set xx = Sheets("Resource Summary 12-13").Range("A1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1")
ARow = 5
Set cll = Sheets("Resource Summary 12-13").Cells(ARow, 1)
Do While Len(cll.Value) > 0
rw = cll.Row
If Evaluate("SUM(--((IF(ISEVEN(COLUMN($D$" & rw & ":$Z$" & rw & ")),'Resource Summary 12-13'!$D$" & rw & ":$Z$" & rw & "))<(IF(ISODD(COLUMN($C$" & rw & ":$Y$" & rw & ")),'Resource Summary 12-13'!$C$" & rw & ":$Y$" & rw & ")*0.85)))=12") Then
With Sheets("Forecasts")
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

[hobbiton73]

Hi @p45cal. Thank you very much for taking the time and trouble to to put this together.

I've added the code to a module within the workbook, but unfortunately I'm unable to run this successfully.

When I run the code, I receive the following error:Run-time error '13': Type mismatch, and in VB Editor it highlights this line as being the source of the problem:

If Evaluate("SUM(--((IF(ISEVEN(COLUMN($D$" & rw & ":$Z$" & rw & ")),'Resource Summary 12-13'!$D$" & rw & ":$Z$" & rw & "))<(IF(ISODD(COLUMN($C$" & rw & ":$Y$" & rw & ")),'Resource Summary 12-13'!$C$" & rw & ":$Y$" & rw & ")*0.85)))=12") Then

Could you tell me please am I doing something wrong?

Many thanks and kind regards

[p45cal]

Which version of Excel?

[hobbiton73]

Hi, thank you for coming back to me so quickly.

I'm using Excel 2003.

Kind regards

[p45cal]

Always useful to know.
Replace that line with: If Evaluate("SUM(--((IF(MOD(COLUMN($D$1:$Z$1)/2,1)=0,'Resource Summary 12-13'!$D$" & rw & ":$Z$" & rw & "))<(IF(MOD(COLUMN($C$1:$Y$1)/2,1)<>0,'Resource Summary 12-13'!$C$" & rw & ":$Y$" & rw & ")*0.85)))=12") Then

[hobbiton73]

Ho @p45cal, thank you very much for this. The copy and paste functionality works absolutely great, thank you!

The only very slight problem is that it copies the whole list from the Source sheet rather than just those which have a cell containing a value which use the calculation `=cell*85%`.

Many thanks and kind regards

[hobbiton73]

Hi @p45cal,

Just thought I'd give you an update. Please ignore my earlier comment, the cell calculation is working fine.

The problem that was causing the confusion, is that the who list is not being copied. i.e. the checking of column stops as row 22 and when the code pastes the data across to the Destination sheet, it starts pasting in column D rather than C.

I hope this helps.

Many thanks and kind regards

[p45cal]

Addressing the first concern:
the checking of column stops as row 22Presumably because there's an empty cell in row 22/23 of column A of the source sheet? If I'm right, that's what you asked for:
starting at row 5 until it reaches a blank cellAre you saying there are blanks interspersed in column A that you need to ignore or jump over?

[hobbiton73]

Hi , firstly, thank you for your continued help with this, it is greatly appreciated. You must be getting fed up of me !

I can confirm that there are no blanks in Column A for rows 5 to 26, but as i said it seems to stop copying after row 22.

I hope this helps.

Many thanks and kindest regards

[p45cal]

The other reason it will stop copying is that those rows do not meet the criteria; might you have some blanks in columns C to Z in rows 22 to 26?

Can you debug by stepping throught the code with F8? If so you could temporarily add a few lines (labelled debug line and red below):Sub blah()
Set xx = Sheets("Resource Summary 12-13").Range("A1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1")
ARow = 5
Set cll = Sheets("Resource Summary 12-13").Cells(ARow, 1)
Do While Len(cll.Value) > 0
cll.Select 'debug line
rw = cll.Row
MsgBox "Criteria for cell values met = " & Evaluate("SUM(--((IF(MOD(COLUMN($D$1:$Z$1)/2,1)=0,'Resource Summary 12-13'!$D$" & rw & ":$Z$" & rw & "))<(IF(MOD(COLUMN($C$1:$Y$1)/2,1)<>0,'Resource Summary 12-13'!$C$" & rw & ":$Y$" & rw & ")*0.85)))=12") & " for row " & cll.Row 'debug line
If Evaluate("SUM(--((IF(MOD(COLUMN($D$1:$Z$1)/2,1)=0,'Resource Summary 12-13'!$D$" & rw & ":$Z$" & rw & "))<(IF(MOD(COLUMN($C$1:$Y$1)/2,1)<>0,'Resource Summary 12-13'!$C$" & rw & ":$Y$" & rw & ")*0.85)))=12") Then
With Sheets("Forecasts")
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)
cll.Select 'debug line
Loop
End Sub
This will select the cell in column A of the source sheet (it should be the active sheet for this exercise) which will help you know what row it's looking at, then when when you step through you'll at least know if it's examining those rows.

[hobbiton73]

Hi, thank you very much for this.

I've adapted the code by including the new lines you kindly posted. There seem to be a number of issues.

Firstly, I was a little unsure about how to use F8, but I hope this will help.

When I run the script although the dialog box says that the row is False, i.e none of the cells within the row contain the specific calculation, it still copies these rows.

Although the copy function reads the correct number of rows i.e. where there is a value in column A, it only pastes from row 5 to 22.

The first paste column seems to be column E rather than C.

Many thanks and kind regards

[p45cal]

Can you send me the file (I will private message here you with an email address)?
Otherwise could you post the file on the interweb somewhere and provide a link yto it here?

[hobbiton73]

Hi, I think I may be able to put it on my server. I'm just about to get ready for work, so I'll have a go at this tomorrow and send you a private message with the link if that's ok?

Kind regards

[p45cal]

Hi, I think I may be able to put it on my server. I'm just about to get ready for work, so I'll have a go at this tomorrow and send you a private message with the link if that's ok?

Kind regards
It's OK.

[hobbiton73]

Hi @p45cal, I'm very sorry I didn't get back to you yesterday with this, I've been quite busy.

I just want to say I really do appreciate you bearing with me on this.

As previously discussed, please find attached my file that I've been using.

Many thanks and kind regards

[p45cal]

When I run the script although the dialog box says that the row is False, i.e none of the cells within the row contain the specific calculation, it still copies these rows.Check carefully the Name of the people in column A, you'll find only those which met the criteria have been copied over.



Although the copy function reads the correct number of rows i.e. where there is a value in column A, it only pastes from row 5 to 22.You said:

For any rows that meet the above criteria, I'd like to copy the cells in columns A, D, F, H , J, L, N, P, R, T, V, X, Z and paste them into the 'Forecasts' sheet in columns A, C, E, G, I, K, M, O, Q, S, U, W and Y. This is exactly what happens.




The first paste column seems to be column E rather than C.
Eh??!
This seems to be working as intended; of 22 people to start with, 5 don't meet the criteria for copying, so you'd only expect 17 to be copied over, when you look at Forecasts, only 17 have been copied over.

[hobbiton73]

Hi @p45cal, my sincere apologies, yes it copies the data across.

The only slight problem is that it hasn't copied 'Person 4', 'Person '8' and 'Person 13' across.

Although there are figures which don't fall within the 'value' criteria, they have at least one cell which does.

Again, my apologies because I've looked at my original post and it didn't make this clear.

Would there be any possibility that this could be changed please so that if any cell within the row falls within the 'value' criteria, that the whole row is copied.

Many thanks and kind regards

[p45cal]

there are 2 instances of
=12
in the code (one in a debug.line), replace them with
>0

[hobbiton73]

Hi @p45cal, this works absolutely brilliant!

Thank you so much for all the time, trouble and effort you out into getting this to work, it is greatly appreciated.

All the very best and kind regrads

[hobbiton73]

Hi, I wonder whether someone amy be able to help me please.

The script provided by @p45cal was more than I could have hoped for, but I'm now wanting to extend this a little further and extend the range of cells which are copied, and hence pasted to the Destination worksheet.

I'd like to include column 'AD' from the 'Source' sheet, to be pasted into column Z on the 'Destination' sheet.

I've made the changes here:

Set xx = Sheets("Resource Summary 12-13").Range("A1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1,AD1")

So that it now includes column 'AD' when it copies the information, but I'm having difficulty is pasting this information to column Z, instead it pastes this in column 'AA'.

I just wondered whether someone may be able to look at this please and offer a little guidnace on how I can change the 'paste' range.

Many thanks and kind regards

[p45cal]

straight after:
DestColm=DestColm+2
add the line:
if DestColm=27 then DestColm=26

[hobbiton73]

Hi @p45cal, thank you very much for taking the time to look at this and for helping me once again.

All the very best and kind regards