VBA how to clear the VBA code to a new open workbook that copy from the Active Workbook

geloader0

Board Regular
Joined
Dec 21, 2022
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
Hi Good day, I have a macro that will copy the data from the ActiveWorkbook using
VBA Code:
Activesheet.copy
. But the VBA code also copied to the new workbook open. Is there any code to clear the VBA code to a new open workbook? Thank you so much
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi geloader0,

if you save the new workbook as xlOpenXMLWorkbook (xlsx) all projects will be deleted.You might need to deactivate DisplayAlerts before and set it back after saving.

Or maybe use

VBA Code:
Sub MrE_1229224_170260C()
Dim wsNew As Worksheet
Dim wsAct As Worksheet

Set wsAct = ActiveSheet
Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))

wsAct.Cells.Copy wsNew.Range("A1")
wsNew.Copy

Set wsNew = Nothing
Set wsAct = Nothing
End Sub

Holger
 
Last edited:
Upvote 0
Hi geloader0,

if you save the new workbook as xlOpenXMLWorkbook (xlsx) all projects will be deleted.You might need to deactivate DisplayAlerts before and set it back after saving.

Or maybe use

VBA Code:
Sub MrE_1229224_170260C()
Dim wsNew As Worksheet
Dim wsAct As Worksheet

Set wsAct = ActiveSheet
Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))

wsAct.Cells.Copy wsNew.Range("A1")
wsNew.Copy

Set wsNew = Nothing
Set wsAct = Nothing
End Sub

Holger

Hi, it works perfect. Anyway, the sheet have also conditional formatting and I want it to delete the conditional formatting but keeping the format.
 
Upvote 0
Hi geloader0,

like this:

VBA Code:
Public Sub MrE_1229224_170260CUpdate()
' https://www.mrexcel.com/board/threads/vba-how-to-clear-the-vba-code-to-a-new-open-workbook-that-copy-from-the-active-workbook.1229224/
' Updated: 20230207
' Reason:  deleting conditional formatting in sheet to export, move the new sheet instead of copy

Dim wsNew As Worksheet
Dim wsAct As Worksheet

Set wsAct = ActiveSheet
Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))

wsAct.Cells.Copy wsNew.Range("A1")
With wsNew
  On Error Resume Next
  .Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
  Err.Clear
  On Error GoTo 0
  .Move
End With

Set wsNew = Nothing
Set wsAct = Nothing
End Sub

Holger
 
Upvote 1
Solution
Hi geloader0,

like this:

VBA Code:
Public Sub MrE_1229224_170260CUpdate()
' https://www.mrexcel.com/board/threads/vba-how-to-clear-the-vba-code-to-a-new-open-workbook-that-copy-from-the-active-workbook.1229224/
' Updated: 20230207
' Reason:  deleting conditional formatting in sheet to export, move the new sheet instead of copy

Dim wsNew As Worksheet
Dim wsAct As Worksheet

Set wsAct = ActiveSheet
Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))

wsAct.Cells.Copy wsNew.Range("A1")
With wsNew
  On Error Resume Next
  .Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
  Err.Clear
  On Error GoTo 0
  .Move
End With

Set wsNew = Nothing
Set wsAct = Nothing
End Sub

Holger

Hi, after deleting the conditional formatting the format of the cells also gone I mean like the color of the cells. I want the format still on the cell but without conditional formatting anymore
 
Upvote 0
Hi geloader0,

doing so we will need information about what CF you are using - I haven't found an easy way yet when it comes to Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets as well as the wide range of colours.

Holger
 
Upvote 0
Hi geloader0,

doing so we will need information about what CF you are using - I haven't found an easy way yet when it comes to Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets as well as the wide range of colours.

Holger

Hi @HaHoBe , already found a solution, I just loop every column then copy the formatting but it took a little time. Thank you again for the helped.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top