Copy specific worksheets values only to a new workbook

s1j44 · Jun 12th 2012

I am a vba rookie and have hit a wall.
Here is the general layout i'm trying to accomplish. I've created a gradebook for a school. Each teacher will have one grade workbook that can contain up to 12 subjects. These subjects are hidden via code unless a class name is entered in a cell on a "menu" page. Each subject has a class grade summary page.

I'm trying to make macro that will do the following:
1) determine if a class name has been entered in a certain cell in the menu page
2) if one has been entered then copy the summary page for that subject into a new workbook (of course only copy values only not formulas)
3) macro needs to do this for each of the 12 cells in the "menu" and return all the needed summary pages to the same new workbook.

that file can then be used to report grades to school office.

Is this possible? the only thing i have gotten to work is to have each summary page copy to its own new workbook and it still has the formulas

i've searched the forum and cant find any code that will do this even after i edit them to fit my layout.

please help.

dangle · Jun 13th 2012

Re: Copy specific worksheets values only to a new workbook

You will have to adjust the workbook and sheet names, and adapt into your code, but something like:

Code

With NewWorkbook
    .Sheets("ClassSummary").UsedRange.Value = .Sheets("ClassSummary").UsedRange.Value
End With

s1j44 · Jun 14th 2012

Re: Copy specific worksheets values only to a new workbook

thanks for trying to help dangle but i was unable to get it to work.
here is what i have so far...

this code will look at my menu cell to make sure its not blank then copy the first summary page

Code

Sub runreport()
Application.ScreenUpdating = False


If ThisWorkbook.Sheets("Grade Book Menu").Range("m3") <> "" Then


Sheet4.Visible = xlSheetVisible
Sheet4.Copy
Sheet4.Visible = xlSheetVeryHidden


End If
Set wb = ActiveWorkbook


End Sub

Display More

so my question is how to i get this to look at multiple cells in the menu and then copy multiple worksheets values only (i'm guessing dangle's code somewhere) to one new workbook?

dangle · Jun 15th 2012

Re: Copy specific worksheets values only to a new workbook

I don't know if this is really what you are looking for and it would help to see a sample file but here is a possible. Note it saves the "Summary" workbook.

Code

Sub runreport()
Dim a, i As Long, wb1 As Workbook, wb2 As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb1 = ThisWorkbook
With wb1
    With Sheets("Menu")
        a = .Range("M3", .Range("M" & Rows.Count).End(xlUp)).Value
    End With
End With
Set wb2 = Workbooks.Add
For i = 1 To UBound(a, 1)
    wb1.Activate
    If SheetExists(Trim(a(i, 1))) Then
        wb1.Worksheets(Trim(a(i, 1))).Visible = True
        With wb2
            wb1.Sheets(Trim(a(i, 1))).Copy Before:=.Sheets(1)
            .Worksheets(Trim(a(i, 1))).UsedRange.Value = .Worksheets(Trim(a(i, 1))).UsedRange.Value
        End With
        wb1.Worksheets(Trim(a(i, 1))).Visible = xlSheetVeryHidden
    End If
Next
With wb2
    .SaveAs wb1.Path & "\Summary"
    .Close
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set wb1 = Nothing
Set wb2 = Nothing
     
End Sub

Display More

s1j44 · Jun 19th 2012

Re: Copy specific worksheets values only to a new workbook

okay dangle your post got me thinking of some other ways to do this it works but it is slow can you think of anyway to speed up the function of this code? (I'm sure this is sloppy code but it does do exactly what i want it to). I'm concerned about the speed of it because it is already slow and I'm only half done. The full code would keep going until it checked for a count of 12 in the counta and copied all 12 sheets.

Code

Sub report()
numsub = WorksheetFunction.CountA(Range("N2:N13"))
Dim sh As Worksheet
If numsub = 0 Then
Exit Sub
End If


Application.ScreenUpdating = False
For Each sh In Sheets(Array(Sheet4.Name, Sheet9.Name, Sheet14.Name, Sheet19.Name, Sheet24.Name, Sheet29.Name, Sheet29.Name, Sheet34.Name, Sheet39.Name, Sheet44.Name, Sheet49.Name, Sheet54.Name, Sheet59.Name))
sh.Visible = xlSheetVisible
Next


If numsub = 1 Then
Sheet4.Copy
End If


If numsub = 2 Then
Sheets(Array(Sheet4.Name, Sheet9.Name)).Copy
End If


If numsub = 3 Then
Sheets(Array(Sheet4.Name, Sheet9.Name, Sheet14.Name)).Copy
End If


If numsub = 4 Then
Sheets(Array(Sheet4.Name, Sheet9.Name, Sheet14.Name, Sheet19.Name)).Copy
End If


If numsub = 5 Then
Sheets(Array(Sheet4.Name, Sheet9.Name, Sheet14.Name, Sheet19.Name, Sheet24.Name)).Copy
End If


If numsub = 6 Then
Sheets(Array(Sheet4.Name, Sheet9.Name, Sheet14.Name, Sheet19.Name, Sheet24.Name, Sheet29.Name)).Copy
End If
Sheet4.Visible = xlSheetVeryHidden
Sheet9.Visible = xlSheetVeryHidden
Sheet14.Visible = xlSheetVeryHidden
Sheet19.Visible = xlSheetVeryHidden
Sheet24.Visible = xlSheetVeryHidden
Sheet29.Visible = xlSheetVeryHidden
Sheet34.Visible = xlSheetVeryHidden
Sheet39.Visible = xlSheetVeryHidden
Sheet44.Visible = xlSheetVeryHidden
Sheet49.Visible = xlSheetVeryHidden
Sheet54.Visible = xlSheetVeryHidden
Sheet59.Visible = xlSheetVeryHidden




Set wb = ActiveWorkbook
noform




Application.ScreenUpdating = True


End Sub


Sub noform()
    Dim wSh As Worksheet
    For Each wSh In ActiveWorkbook.Worksheets
        With wSh.UsedRange
            .Copy
            .PasteSpecial xlPasteValues
        End With
    Next wSh
     
    Application.CutCopyMode = False
End Sub

Display More

s1j44 · Jun 20th 2012

Re: Copy specific worksheets values only to a new workbook

would it run faster if i divided each show--copy--hide action in to different subs and then reference them in the if counta is 1 then do this, if counta is 2 then do that, etc.?

dangle · Jun 21st 2012

Re: Copy specific worksheets values only to a new workbook

There may be slightly better ways of doing this, such as using a 'select case' statement rather than multiple 'if' statements and organising the unhiding/hiding of sheets, maybe use some additional subs, but I am not sure that this is your issue if you are saying it is noticeably slow.
How about:

Code

Application.Calculation = xlCalculationManual
'Your code
Application.Calculation = xlCalculationAutomatic

s1j44 · Aug 14th 2012

Re: Copy specific worksheets values only to a new workbook

sorry for the slow response... I was moving accross the country

It works great thanks so much.

Copy specific worksheets values only to a new workbook

Participate now!

Share