Macro CopySheets Challenge

Help Activities
, ,
1

I need to move and overwrite a worksheet, from one excel to another, but when I carry out this movement, the target worksheet loses the formula references, follow the vba code that I am using.

Before Copy:
image

After Copy:
image

As you can see, the target worksheet lost the formula reference of the “TOGETHER” sheet, to solve this I tried to disable the automatic calculation before copying the sheets, but without success: Here’s the VBACode:

Function SheetsCopy(path1 As String, path2 As String) As String
On Error GoTo label:

Dim macroReturn As String
Dim wb As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet

macroReturn = “Success”
Set wb = Workbooks.Open(path1)
Set wb2 = Workbooks.Open(path2)
Set ws = wb.Worksheets(“FLUXO”)

ws.Activate
ws.EnableCalculation = False

wb2.Worksheets(“TOGETHERNEW”).Copy Before:=wb.Worksheets(“TOGETHER”)
wb.Worksheets(“TOGETHER”).Delete
wb.Worksheets(“TOGETHERNEW”).Name = “TOGETHER”
ws.EnableCalculation = True

copySheets = macroReturn

Exit Function

label:
macroReturn = Err.Description
copySheets = macroReturn

End Function

End Function

2

@Israel_Silva

Did you try with copy sheet activity?

Or did you try using rwad range and then write the range to different workbook?

If only macro is preferred then try this change


ws.EnableCalculation = False

wb2.Worksheets(“TOGETHERNEW”).Cells.Value = wb.Worksheets(“TOGETHER”).Cells.Value
wb.Worksheets(“TOGETHER”).Delete
wb2.Worksheets(“TOGETHERNEW”).Name = “TOGETHER”
ws.EnableCalculation = True

wb2.Worksheets("TOGETHERNEW").Move Before:=wb.Worksheets("TOGETHER")

Cheers

3

hi,
I was using the Copy Sheet activity, but the result is the same, I have also tried to copy all the sheets to a new worksheet, but the worksheet is extremely complex and always copies missing formatting and so on.

Anyway, I will test your correction

Tks!!

1 Like