Richard has attended:
Excel VBA Intro Intermediate course
Offset - pasting same data to multiple cells - next to eachother
Hi there,
I've copied my code into this question box, the code should read as below, but its been put onto two lines through pasting it into this box - but you probably knew that anyway.
Selection.Offset(4, -1).PasteSpecial Paste:=xlPasteValues
My question is, I have altered the selection.offset part of the code, so that it copies down a further 19 cells, but im sure theres a more professional way to accomplish this, without just repeating the code, offset by another row down - ?
Please could you let me know how to do this?
Thanks for your help & hope I've been clear.
Sub CopyData()
Dim Counter As Long
Range("B1").Select
For Counter = 1 To 30000
If ActiveCell.Value Like "*Customer Name*" Then
ActiveCell.Copy
Selection.Offset(4, -1).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 1).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next Counter
End Sub
RE: Offset - pasting same data to multiple cells - next to eacho
Hi Richard
Thanks for the question. I think I understand what you want.
I understand that if the activecell matches a condition you copy it then you want to paste special the value into 19 other cells. Are those other cells adjacent to each other?
If they are then all you need to calculate is the top left and bottom right corners and use range with two references.
ActiveCell.Copy
Range(ActiveCell.Offset(4,-1),Activecell.offset(23,-1)).PasteSpecial Paste:=xlPasteValues
the above should do the same as your copy and pastes.
Does that help?
Laura GB
RE: Offset - pasting same data to multiple cells - next to eacho
Hi Laura,
thanks for your info and yes the cells are adjacent to eachother. I have altered the code as you described, but now i get an error, here is the altered code:
Sub CopyData()
Dim Counter As Long
Range("A1").Select
For Counter = 1 To 30000
If ActiveCell.Value Like "*Customer Name*" Then
ActiveCell.Copy
Range(ActiveCell.Offset(4, -1), ActiveCell.Offset(23, -1)).PasteSpecial Paste:=xlPasteValues
' Selection.Offset(4, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
' ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next Counter
End Sub
I've put my old long-winded code into comments, but when the procedure reaches line: 'Range(ActiveCell.Offset(4, -1), ActiveCell.Offset(23, -1)).PasteSpecial Paste:=xlPasteValues'
it causes error: Run-time rror '1004': Application-defined or object-defined error
Its hopefully something simple, (perhaps I didnt tell you something I should have) - any ideas??
RE: Offset - pasting same data to multiple cells - next to eacho
Hi Richard
The code we started from had Range("B1"). Select at the beginning. This meant that when you do and Offset(4,-1), which means 4 rows down and one column to the left you can do it.
You have changed it to now look at Range("A1").Select. You cannot refer to the column to the left because the A column doesn't have one.
You either need to change back to B1 or you need to recalculate your range to paste into.
Hope that helps
Laura
