exporting multiple sheets to new workbook as values and source formatting

[psg2002]

Hi,
I am able to run my VBA code smoothly. However, I am not sure how to paste them as values and keep the source formatting.

Sub ExportWorksheets()
    
    Dim wbSource As Workbook, wbTarget As Workbook
    Dim worksheetList As String 
    Dim worksheetArr As Variant
    Dim arrIndx As Long


    worksheetList = "GOOGL:TSLA"
    
    worksheetArr = Split(worksheetList, ":")
    
    If UBound(worksheetArr) = -1 Then Exit Sub
    
    Set wbSource = ThisWorkbook
    
    Set wbTarget = Workbooks.Add
    
    For arrIndx = LBound(worksheetArr) To UBound(worksheetArr)
    ThisWorkbook.Worksheets(worksheetArr(arrIndx)).Copy wbTarget.Worksheets(wbTarget.Worksheets.Count)
    
    Next arrIndx
    
     MsgBox "Export complete.", vbInformation
    
CleanObjects:
    Set wbTarget = Nothing
    Set wbSource = Nothing


End Sub

I appreciate your help in advanced!

 

[Zot]

I believe this reference will solve your problem

VBA Paste Special

Like a worksheet, we have a Paste Special method to copy data and paste it on a different cell range. It allows us to paste the data as itself or only the

www.wallstreetmojo.com

 

[psg2002]

I believe this reference will solve your problem

VBA Paste Special

Like a worksheet, we have a Paste Special method to copy data and paste it on a different cell range. It allows us to paste the data as itself or only the

www.wallstreetmojo.com

My code stops at the following red line not sure what i am doing wrong

Sub ExportWorksheets()
   
    Dim wbSource As Workbook, wbTarget As Workbook
    Dim worksheetList As String
    Dim worksheetArr As Variant
    Dim arrIndx As Long


    worksheetList = "GOOGL:TSLA"
   
    worksheetArr = Split(worksheetList, ":")
   
    If UBound(worksheetArr) = -1 Then Exit Sub
   
    Set wbSource = ThisWorkbook
   
    Set wbTarget = Workbooks.Add
   
    For arrIndx = LBound(worksheetArr) To UBound(worksheetArr)
    ThisWorkbook.Worksheets(worksheetArr(arrIndx)).Copy wbTarget.Worksheets(wbTarget.Worksheets.Count)
    wbtarget.worksheets(worksheetarr(arrindx).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False

    Next arrIndx
   
     MsgBox "Export complete.", vbInformation
   
CleanObjects:
    Set wbTarget = Nothing
    Set wbSource = Nothing


End Sub

 

[Zot]

Looking at this section:

For arrIndx = LBound(worksheetArr) To UBound(worksheetArr)
ThisWorkbook.Worksheets(worksheetArr(arrIndx)).Copy wbTarget.Worksheets(wbTarget.Worksheets.Count)
wbtarget.worksheets(worksheetarr(arrindx).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False

The Bold Red command is already paste destination. Then on the next line you were pasting again. The Bold RED line should not be there.