vba code to copy worksheets and paste as values.

[randyharris]

I know that I could do this for a specific workbook, but it would be less efficient as a recorded macro, and it wouldn't work for any workbook.

I have the need to start a macro that goes through all worksheets in a workbook (it can ignore hidden worksheets) and select the entire worksheet, copy, then paste as values.

So it would start at the first say sheet1, copy the whole sheet, paste as values, then go to sheet2, copy, paste as values, etc..

Any help would be appreciated.

Randy

 

[Andrew Poulsom]

Please post your VBA code.

 

[vba_beginner1]

here is my code. i have added other modules inside the code to suit my other needs too. the same code in the other unprotected version ran without any trouble.

Sub Test()


Dim DestinationBook As Workbook
Dim strSaveName As String
Dim ws As Worksheet
Dim collValues As Collection
Dim rCell As Range
Dim i As Integer, icolumn As Integer
 


    With ThisWorkbook
' my function to unprotect the sheet
    ActiveWorkbook.UnProtect
        .Worksheets("SMOE-FRONT").Copy
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
        .Worksheets("SMOE-BACK").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
    End With
    Windows(GeneratorFile).Activate
    ActiveWorkbook.Protect
    'get the name to save
    Windows(GeneratorFile).Activate
    strSaveName = Worksheets("Configuration").Range("I56").Value
    Application.Dialogs(xlDialogSaveAs).Show (strSaveName)
    
    'This block reactivates the string counting in the label text
    Sheets("SMOE-FRONT").Select
    Range("T24:T25").Select
    ActiveCell.FormulaR1C1 = "=LEN(RC[-10])"
    Range("T24:T25").Select
    ActiveWindow.SmallScroll Down:=6
    Selection.AutoFill Destination:=Range("T24:T41"), Type:=xlFillDefault
    Range("T24:T41").Select
    ActiveWorkbook.Save
   
'To remove the blanks between lines
Set ws = Sheets("SMOE-FRONT")
For icolumn = 3 To 8
    If Application.WorksheetFunction.CountA(ws.Range(ws.Cells(23, icolumn), ws.Cells(52, icolumn))) > 1 Then
        Set collValues = New Collection
        For Each rCell In ws.Range(ws.Cells(23, icolumn), ws.Cells(52, icolumn)).SpecialCells(xlCellTypeConstants)
            collValues.Add rCell.Value
        Next rCell
        ws.Range(ws.Cells(23, icolumn), ws.Cells(52, icolumn)).ClearContents
        For i = 1 To collValues.Count
            ws.Cells(53, icolumn).End(xlUp).Offset(1, 0).Value = collValues.Item(i)
        Next i
        Set collValues = Nothing
    End If
Next icolumn
    Application.CutCopyMode = False
    'end of code to remove blanks
    ActiveWorkbook.Save
    ActiveWindow.Close
Windows(GeneratorFile).Activate
ActiveWorkbook.protect
Sheets("Selector Homepage").Select
End Sub

 

[Andrew Poulsom]

Instead of:

ActiveWorkbook.Unprotect

I expect you need you need eg:

.Worksheets("SMOE-FRONT").Unprotect

 

[bepedicino]

I am a newbiee with no VBA experince. I have the following code that copies all sheets and then paste it as values. I would some assistance if someone could please modify the code to unhide all hidden columns first and then past all as values. If possible, I would then like it to delete all sheets other than the one that I have selected.

Any help would be greatly appreciated!

Sub Test()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Visible = True Then
Sh.Activate
Sh.Cells.Copy
Sh.Range("A1").PasteSpecial Paste:=xlValues
Sh.Range("A1").Select
End If
Next Sh
Application.CutCopyMode = False
End Sub

 

[Andrew Poulsom]

Please start your own thread rather than hijacking someone else's.

 

[bepedicino]

My apology for the oversiight. I am new to messsage boards.

 

[Anastasiya19]

Try:

Sub Test()
    With ThisWorkbook
        .Worksheets("SMOE-FRONT").Copy
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
        .Worksheets("SMOE-BACK").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
    End With
    Application.CutCopyMode = False
End Sub

Hi Andrew,

May you please help me. I am new to VBA and want to do the same but paste the copy in the same workbook but different sheet with a specific name let's say tab "T-1".

Thank you!