98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Offset - pasting same data to multiple cells - next to eachother
Offset - pasting same data to multiple cells - next to eachother
Resolved · Medium Priority · Version 2003
Wed 17 Sep 2008, 19:48Delegate Richard said...
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
Fri 26 Sep 2008, 12:41Trainer Laura said...
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
Mon 6 Oct 2008, 16:52Delegate Richard said...
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??
Mon 6 Oct 2008, 17:28Trainer Laura said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Checking if a calculation adheres to Order of PrecedenceWhen writing formulas you must make sure that results will be calculated as you intended. |
