VBA help

[Giggzz]

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "national"
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
With Range("V:V")
.EntireColumn.ClearContents
Dim FilterRange As Range, PrintRange As Range
Set FilterRange = Range(("A4"), cells(Rows.Count, 1).End(xlUp).Offset(0, 22))
Set PrintRange = Range(("A1"), cells(Rows.Count, 1).End(xlUp).Offset(0, 20))
Range(("V4:V63"), cells(Rows.Count, 1).End(xlUp).Offset(0, 22)).Formula = "=IF(SUM(RC2,RC6,RC10,RC14)>0,""Yes"",""No"")"
Range("V4,V62:V64") = "Yes"
.Value = .Value
FilterRange.AutoFilter Field:=22, Criteria1:="Yes"
PrintRange.PrintOut
ActiveSheet.AutoFilterMode = False
.EntireColumn.ClearContents
End With
Set FilterRange = Nothing
Set PrintRange = Range(("A63"), cells(Rows.Count, 1).End(xlUp).Offset(0, 21))
Application.ScreenUpdating = True
ActiveSheet.Protect "national"
End Sub

THe above code prints only the information I need, but what Im looking to do is have the information copied to a blank worksheet. What mod would have to be done to have this happen?

 

[Norie]

Giggzz

I'll take a look at your code tomorrow, it's late here.

But one thing you could try doing is using code tags.

It makes it easier to follow/understand the code and will increase the chances of you getting help.

 

[xenou]

Maybe something like this -- call this subroutine with your PrintRange instead of MyRange as in the example:

Sub testit()
Dim MyRange As Range

    Set MyRange = Range("A1:A10")
    Call ExportToWorksheet(MyRange, ActiveSheet)

End Sub
'-------------------------------------------------
Private Sub ExportToWorksheet(ByRef Arg1 As Range, wsCopiedFromWorkSheet As Worksheet)
Dim ws As Worksheet
    
    Arg1.Copy
    Set ws = Workbooks.Add.Worksheets(1)
    ws.Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False

    wsCopiedFromWorkSheet.Activate

End Sub

There must be alternative solutions, I am sure... I've taken the precaution of activating the original worksheet so that your remaining code will continue to work on the assumption that that is still the activesheet.

HTH