Hi all,
I hope everyone's doing well! I've come up with something I want to do and have written out what I think the code may roughly need to look like
(using bits and bobs from previous help I've gotten here on Mr Excel),
but my skills unfortunately aren't at the point yet where I know how to write it so that it works. So I hoped maybe one of you can tell me where
I went wrong, how I can write it correctly.
So I have a set of data that looks like this:
Now, I want to have a macro that copies over the three cells on the left (i.e. the comic names) to another sheet if the value of the relavant
cell to the right of them is larger than 0. Which one is the relavant cell, I specify by writing the column number into another cell, say cell A1 on Sheet2.
The macro would then move onto the next row and do the same, copying the three cells in columns A B and C over into the three cells adjacent to the ones that
the above row was previously copied into. So for the above data, that row in sheet3 (target sheet) would look like this if cell A1 in sheet2 says 4:
I imagine the code would need to look a bit like what I have below (I have to run this through 250 rows btw), would be super grateful for any suggestions how to make it work:
Thanks!
I hope everyone's doing well! I've come up with something I want to do and have written out what I think the code may roughly need to look like
(using bits and bobs from previous help I've gotten here on Mr Excel),
but my skills unfortunately aren't at the point yet where I know how to write it so that it works. So I hoped maybe one of you can tell me where
I went wrong, how I can write it correctly.
So I have a set of data that looks like this:
Now, I want to have a macro that copies over the three cells on the left (i.e. the comic names) to another sheet if the value of the relavant
cell to the right of them is larger than 0. Which one is the relavant cell, I specify by writing the column number into another cell, say cell A1 on Sheet2.
The macro would then move onto the next row and do the same, copying the three cells in columns A B and C over into the three cells adjacent to the ones that
the above row was previously copied into. So for the above data, that row in sheet3 (target sheet) would look like this if cell A1 in sheet2 says 4:
I imagine the code would need to look a bit like what I have below (I have to run this through 250 rows btw), would be super grateful for any suggestions how to make it work:
VBA Code:
Sub copy_cartoons()
Dim k As Long
Dim j As Variant
j = sheets("Sheet2").Range("A1").Value
With Sheets("Sheet1")
For k = 2 To 251
if .Cells((k,j).Value > 0 Then
.Range("A" & k).Resize(, 3).Copy Sheets("Sheet3").Cells(4, k * 3 - 1)
Next k
Next j
End With
End Sub
Thanks!
Last edited by a moderator: