Consulting

Results 1 to 4 of 4

Thread: Copy Range from active row to new sheet

  1. #1

    Copy Range from active row to new sheet

    Ok folks, needing a wee bit of help with this. My skills arent the greatest but I have worked this so far - and just need help to finish.

    My VB Code is as follows

    Sub CopySheet()
        Dim wsMaster2 As Worksheet
        Application.ScreenUpdating = False
        Sheets("MASTER").Copy After:=Sheets(2)
        With Sheets("Master (2)")
            Sheets("Diary System").Range("B5:H5").Copy
            Range("H12").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            .Range("H12,C8") = "123"
            .Range("I12,B5") = "ham"
            .Range("C9") = "Await RM Update"
            .Range("F8:F9") = "Yes"
            .Range("M12,C16,N12,C15") = "6/10/2014"
            With .Range("H12:N12")
            .ClearContents
            End With
            .Visible = False
        End With
        Sheets("Diary System").Select
        Application.ScreenUpdating = True
        
    End Sub
    I need to amend it a bit so that instead of copying this line "Sheets("Diary System").Range("B5:H5").Copy" - It will copy those cells from the active row each time.

    Also - not sure how to make it copy the value each time rather than the value thats already in the VB

  2. #2
    Doh! almost there but hit another snag....

    Amended the code like this
    Sub CopySheet()
        Dim wsMaster2 As Worksheet
        Application.ScreenUpdating = False
        Sheets("MASTER").Copy After:=Sheets(2)
        With Sheets("Master (2)")
            Sheets("Diary System").Range("B5:H5").Copy
            Range("H12").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            .Range("C8") = (H12)
            .Range("B5") = (I12)
            .Range("C9") = (J12)
            .Range("F8") = (K12)
            .Range("F9") = (L12)
            .Range("C16") = (M12)
            .Range("C15") = (N12)
            With .Range("H12:N12")
            .ClearContents
            End With
            .Visible = False
        End With
        Sheets("Diary System").Select
        Application.ScreenUpdating = True
        
    End Sub
    But when it does the .clear contents it doesnt retain the info in the other cells.

  3. #3
    Slowly but steadily fixing it. I now have the following and my only problem is how to get it to select the range based on the active row.

    [VB]Sub CopySheet()
    Dim wsMaster2 As Worksheet
    Application.ScreenUpdating = False
    Sheets("MASTER").Copy After:=Sheets(2)
    With Sheets("Master (2)")
    Sheets("Diary System").Range("B5:H5").Copy
    Range("H12").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Range("H12").Copy
    Range("C8").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Range("I12").Copy
    Range("B5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    .Range("J12").Copy
    Range("C9").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Range("K12").Copy
    Range("F8").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Range("L12").Copy
    Range("F9").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Range("M12").Copy
    Range("C16").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Range("N12").Copy
    Range("C15").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveSheet.Name = [B5]
    .Visible = False
    End With
    Sheets("Diary System").Select
    Application.ScreenUpdating = True

    End Sub
    [/VB]

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,769
    Avoid using 'copy' to copy values only.

Posting Permissions

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