Macro that will copy values in Column J and paste values to Column B in new sheet

Phixtit
06-16-2010, 05:01 AM
I have a macro that will copy Sheet, Create & name a new Sheet & Paste sheet to the new Sheet.

When it copies Sheet I need it to move the values in column J and Paste them into Column B.

Here is my module code: (Shown in Red is what I've been trying)

Sub MyButtons()

Dim CurrentDay As Integer, NewName As String
Dim checkWs As Worksheet, OldWs As Worksheet
Set OldWs = ActiveSheet
ActiveSheet.Unprotect ("1212")
If IsNumeric(Right(ActiveSheet.Name, 2)) Then
CurrentDay = Right(ActiveSheet.Name, 2)
ElseIf IsNumeric(Right(ActiveSheet.Name, 1)) Then
CurrentDay = Right(ActiveSheet.Name, 1)
Else
Exit Sub
End If
If CurrentDay >= 30 Then
MsgBox "You cannot go higher than 30"
Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = "DAY " & CurrentDay
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
Worksheets(ActiveSheet.Name).Copy After:=Worksheets(ActiveSheet.Index)
With ActiveSheet
.Name = NewName
.Range("B7:B1048576").ClearContents

ActiveSheet.Unprotect ("1212")
.Range("J7:J1048576").Copy
.Range("B7:B1048576").Paste

ActiveSheet.Protect ("1212")
End With
Else
Set checkWs = Nothing
MsgBox "A Worksheet named " & NewName & " already exists."
End If
ActiveSheet.Protect ("1212")
OldWs.Protect ("1212")
End Sub
Below is the form code to call the module above:
Private Sub Day2_Click()
Dim TargetSheet As Worksheet
On Error Resume Next
Sheet2.Select
Set TargetSheet = Sheets("Day 2")
On Error GoTo 0
If TargetSheet Is Nothing Then

If MsgBox("Would you like to add a Day?", vbOKCancel) = vbOK Then

Call MyButtons
Unload Me
Else
Exit Sub

End If
MsgBox "You have successfully added a day."
Else
Unload Me

End If
End SubI have a crosspost here (http://www.mrexcel.com/forum/showpost.php?p=2345035&postcount=1) & nobody has answered.

Any ideas on how this can be accomplished?

ZKat
06-16-2010, 10:15 AM
Hi Phixtit

Change this

.Range("B7:B1048576").Paste

To:
ActiveSheet.Unprotect ("1212")
.Range("J7:J1048576").Copy
.Range("b7").PasteSpecial (xlPasteValues)

Should work fine now.

Phixtit
06-16-2010, 02:58 PM
Thanks,
Here is the solution.
.Range("B7:B1048576").Value=.Range("J7:J1048576").value

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum