Copy & PasteSpecial issue

Hi Guys

I am hoping someone can assist

I have a Master xls which generates 4x worksheets of "raw data" for importing into a SQL file at the End of the Month.

I have generated the code below to copy the required worksheets from the master file and create a new file with only the four worksheets, (this works 100% fine) the problem is that when it copies the worksheets into a new workbook, the cells are still linked to the master.

I need the data in the new worksheets to be only values and formats as the file is to be emailed for uploading - but I couldnt work out a way to do that! I attempted to copy / pastespecial the cells after the new file was created however it crashes with Cells.Select command.

Can anyone provide some guidance? Perhpas a way to create the the new file with vlaues / formats only OR a correct bit of code to copy/ paste special in the new worksheet.


Grateful for any assistance
Boris.

    ' get name to save new workbook as. change reference as needed
    strSaveName = TargetFilePath + "BSC SQL Import File for Month of " & BSCM & " " & BSCY & ".xls"
         
    ' copy sheets to new workbook
    Sheets(Array("BSC SQL Import-Labels(Nat-Reg)", "BSC SQL Import-Labels(AllWrkGp)", "BSC SQL Import(NationalRegions)", "BSC SQL Import(ALLWorkgroups)")).Copy
    ActiveWorkbook.SaveAs strSaveName
     
    Sheets(Array("BSC SQL Import-Labels(Nat-Reg)", "BSC SQL Import-Labels(AllWrkGp)", "BSC SQL Import(NationalRegions)", "BSC SQL Import(ALLWorkgroups)")).Select
    Sheets("BSC SQL Import-Labels(Nat-Reg)").Activate

    Cells.Select ' IT CRASHES HERE!!!
    
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
    ActiveWorkbook.Close

Possibly using a variant array will work instead of copy/pastevalues...

    Dim vData            As Variant
    With Sheets("BSC SQL Import-Labels(Nat-Reg)")
        .Activate
        vData = .UsedRange
        .UsedRange = vData
    End With

Dangelor....
This works perfectly!! I was unsure of how to do it for all sheets effectively but used you code below four times (once for each of the sheets) and it works perfect!!

Many thanks for the assist! Amazing

Boris

Originally Posted by dangelor

Possibly using a variant array will work instead of copy/pastevalues...

    Dim vData            As Variant
    With Sheets("BSC SQL Import-Labels(Nat-Reg)")
        .Activate
        vData = .UsedRange
        .UsedRange = vData
    End With

Glad it worked!

Thanks dangelor -I'll give this a go !!
Cheers
Boris