Loops for copy and paste

  • Thread starter Thread starter carlos_ray86
  • Start date Start date
C

carlos_ray86

I'm trying to do a copy and paste. However, I need the paste to paste
in a different row so they don't overlay. I have been trying to do
for
loops so the the loop would cause the row number to change (i.e. A3
A4
A5). But no success. Please help.


this is the base of my script so far

Sub PasteSpecial()
Dim DeleteRows As Range
Range("A1:A3").Copy
Range("C4").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Set DeleteRows = Range("A1:A8") ' this deletes the row that I just
copied. i.e. cut
DeleteRows.Delete Shift:=xlShiftUp
End Sub


This works great but it will overlap, paste over paste, becuase I
have
the second range set to C4.


I want some how for the C4 to change after every copy so it would be
like this...
Range("A1:A3").Copy
Range("C4").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Range("A1:A3").Copy
Range("C5").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Range("A1:A3").Copy
Range("C6").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
 
I'm trying to do a copy and paste. However, I need the paste to paste
in a different row so they don't overlay. I have been trying to do
for
loops so the the loop would cause the row number to change (i.e. A3
A4
A5). But no success. Please help.


this is the base of my script so far

Sub PasteSpecial()
Dim DeleteRows As Range
Range("A1:A3").Copy
Range("C4").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Set DeleteRows = Range("A1:A8") ' this deletes the row that I just
copied. i.e. cut
DeleteRows.Delete Shift:=xlShiftUp
End Sub


This works great but it will overlap, paste over paste, becuase I
have
the second range set to C4.


I want some how for the C4 to change after every copy so it would be
like this...
Range("A1:A3").Copy
Range("C4").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Range("A1:A3").Copy
Range("C5").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Range("A1:A3").Copy
Range("C6").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
Click to expand...

Is this perhaps what you want?

*********************
Sub TransposeStuff()

Dim i As Integer

For i = 1 To 10
ActiveSheet.Cells(i, 3).Resize(1, 3) = _
Application.Transpose(ActiveSheet.Range("A1:A3"))

ActiveSheet.Range("A1:A3").Delete (xlUp)

Next i

End Sub
*********************

I hope you understand that all "Cells", "Resize" and "Range"-objects can
be customized to your need? Currently this macro takes three values from
a list in column "A" (A1:A3) and transposes them into C1:E1. It then
deletes the three values that were copied to the new location. Next
couple of 3 cells is transposed in C2:E2 etc.

CoRrRan
 
Back
Top