Setting a range within a loop variable for copy/paste

[Craig]

i'm trying to select the range of cells within a row to copy to another
sheet and transpose, it's in the middle of some code that runs a loop
to create new sheets based on entries on the first sheet. It keeps
bombing out on the paste command, and i can't figure out why, is it
clearing the clipboard when the new sheet is created so there's nothing
to paste or is something else wrong, i can't figure it out, any ideas,
here's what i have so far, it flags the last line
(selection.pastespecial). thanks in advance for any help

Sub newSheet()
Dim MyRange As Range
Dim C As Range
Dim NewSheetName As String
Dim newSheet As Worksheet
Dim xLastRow As Long
xLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A6:A" & xLastRow)
For Each C In MyRange
NewSheetName = C.Value
C.Select
Rows(ActiveCell.Row).Select
Selection.Copy
Set newSheet = Sheets.Add
With newSheet
.Move After:=Worksheets(Worksheets.Count)
On Error Resume Next
.Name = NewSheetName
On Error GoTo 0
End With
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next C

End Sub

 

[Guest]

Craig,
Excel doesn't allow you copy a whole row and paste to a given
cell i.e C5 in your case. If you change "Range("C5") to rows("5:5") it will
copy the data ALTHOUGH not as you want!


Try something along these lines -it copies cells A to Z rather than whole row

HTH

Sub newSheet()
Dim MyRange As Range
Dim C As Range
Dim NewSheetName As String
Dim newSheet As Worksheet, ws1 as worksheet
Dim xLastRow As Long

Set ws1 = Worksheets("sheet1") ' <=== Change as required
With ws1
xLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = .Range("A6:A" & xLastRow)
End With

For Each C In MyRange

NewSheetName = C.Value
Set newSheet = Sheets.Add
With newSheet
.Move After:=Worksheets(Worksheets.Count)
On Error Resume Next
.Name = NewSheetName
On Error GoTo 0
End With

ws1.Range("a" & C.Row & ":Z" & C.Row).Copy <===== Change as required
With Worksheets(NewSheetName)
.Range("c5").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Next C

End Sub

 

[Craig]

that's pretty much what i've decided i'd have to do, but instead of
going down to the C in columns A-Z, i want to select the data in the
current C row from A thru the last used cell. is that possible?

 

[Craig]

oh wait, i've figured it out, thanks a lot