Macro to copy data

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
I have numbers in Col B followed by a full stop eg 1. 20. etc


I have tried to write code to copy the value in Col K that corresponds with 30. (same row) in Col B to Col K that corresponds with 25. in Col B same row


For eg the value in col K for 30. is K40 and the value is 108,965 and this must be copied as a value using paste special values in Col k in the same row as 25.

I get paste special class failed when running my macro


See my code below

it would be appreciated if someone could amend my code


Code:
 Sub Compute_Opening_inventory()

Dim lRow As Long, I As Long
lRow = Cells(Rows.Count, 11).End(xlUp).Row
For I = 1 To lRow
If Left(Cells(I, 1), 3) = "30." Then
   Cells(I, 11).Copy
End If
If Left(Cells(I, 1), 3) = "25." Then
    Cells(I, 11).PasteSpecial xlValues
   Application.CutCopyMode = False
    
End If
Next

End Sub
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
can you post a sample....what you have provided is confusing...at least to me anyway !!
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the reply Michael


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25.</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Finished goods</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">987,145</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">27.</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">28.</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">29.</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">30.</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">Finished goods</td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">920,250</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />


in this example, I want to copy K7 which is in the same row as 30. in Col B to K1, which in same row as 25. in Col B
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
Hi Michael


Have you had a chance to look at the sample data I posted ?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
No, not really.....but on quick inspection, I'm assuming you mean Col L no Col K ???
AND
the value in "30." should overwrite the value in "25." ??
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,639
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the reply


My apologies

You are 100% correct. It should be Col L and the value in "30." should overwrite the value in "25."
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top