MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copying data only

  Asked By: Karina    Date: Dec 25    Category: MS Office    Views: 501

I have a workbook that I am currently, actively developing VBA macros
in. Since it is being actively used (because of time constraints), it
contains information that I need to keep. Yet, things "can happen" as
I continue programming the VBA. For this reason, I would like to be
able to copy all of the sheets from this application to a data-only
(without the VBA) Excel workbook.
Is there a way to do that? I would like to keep the sheet names the
same, too, so I can use my current code with as little changes as
possible (and to make it more readable when I am looking at the sheet



2 Answers Found

Answer #1    Answered By: Muriel Dunn     Answered On: Dec 25

if your talking just a one time  copy .. easy... select the sheet  tabs you want
to copy... right click on the tab and in the menu select copy/move in the pop
up select make copy  and the in the drop down along the top select new
workbook... click ok and it creates the new workbook  with the sheets  you
selected and the sheet names  as well.. if you want to automate it or do it
multiple times.. record the macro of you doing the steps and then you can just
run the macro..

Answer #2    Answered By: Trae Thompson     Answered On: Dec 25

The problem with just copying  the sheets  to a new workbook  is that any VBA code
in the sheet  modules for those sheets gets copied too. The following code  copies
all the sheets in the active workbook to a new workbook, then removes all VBA
code from the new workbook. I found the code to remove all macros from a
workbook at the Erlandsen Data Consulting site (lots of great stuff there!)

Sub CopySheetsNotCode()
Call RemoveAllMacros(ActiveWorkbook)
End Sub

Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveDocument ' in Word
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
Dim i As Long, l As Long
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub

Didn't find what you were looking for? Find more on Copying data only Or get search suggestion and latest updates.