+ Reply to Thread
Results 1 to 4 of 4

Setting a range within a loop variable for copy/paste

  1. #1
    Craig
    Guest

    Setting a range within a loop variable for copy/paste

    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


  2. #2
    Toppers
    Guest

    RE: Setting a range within a loop variable for copy/paste

    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" wrote:

    > 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
    >
    >


  3. #3
    Craig
    Guest

    Re: Setting a range within a loop variable for copy/paste

    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?


  4. #4
    Craig
    Guest

    Re: Setting a range within a loop variable for copy/paste

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1