PDA

View Full Version : Copy Range from active row to new sheet



hmltnangel
06-03-2014, 02:47 AM
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

hmltnangel
06-03-2014, 02:55 AM
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.

hmltnangel
06-03-2014, 04:47 AM
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.

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

snb
06-03-2014, 07:09 AM
Avoid using 'copy' to copy values only.