Getting blank sheet when copying to new workbook

[draziw]

Hi,
I have some code to massage excel workbook in an access VBA script.
I copied some code from this site re-copying sheet to new workbook which I have been attempting to modify.

At this stage I have two problems.

1(. The new workbook has no data.
2(. I get a pop up message saying that I have a large amount of data in the clipboard, (I thought this code disabled that)

 Dim excelapp As Object, wb As Object, ws As Object, ws2 As Object
 
 excelapp.CutCopyMode = False

Here is the code of the subroutine.

Sub MySheetCopy(SBName)

    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim myNewFileName As String
      Dim stPathName As String
      Dim excelapp As Object, wb As Object, ws As Object, ws2 As Object
   Set excelapp = CreateObject("excel.application")
    
    '   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet
   stPathName = "C:\RSL LogBookMgmt\LogBook files\Reports\"

'   Build new file name based
    myNewFileName = stPathName & SBName & " Monthly Charitable Activities Form.xlsx"
 
   Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=myNewFileName
    Set myDestWB = ActiveWorkbook

'   Copy over sheet from previous file
    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1").Select
    'ActiveSheet.Paste
    With Workbooks.Add.Sheets(1).Range("A1")
   .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  
   End With
   excelapp.CutCopyMode = False


   mySourceWB.Close savechanges:=True
   myDestWB.Close savechanges:=True
End Sub

 

[johnnyL]

Couple things for you at a quick glance.

myNewFileName = stPathName & SBName & " Monthly Charitable Activities Form.xlsx"

What is SBName supposed to be? Right now it is nothing apparently.



Change:

excelapp.CutCopyMode = False

To:

Application.CutCopyMode = False

Delete the following:

      Dim excelapp As Object, wb As Object, ws As Object, ws2 As Object
   Set excelapp = CreateObject("excel.application")

 

[draziw]

Couple things for you at a quick glance.

myNewFileName = stPathName & SBName & " Monthly Charitable Activities Form.xlsx"

What is SBName supposed to be? Right now it is nothing apparently.



Change:

excelapp.CutCopyMode = False

To:

Application.CutCopyMode = False

Delete the following:

      Dim excelapp As Object, wb As Object, ws As Object, ws2 As Object
   Set excelapp = CreateObject("excel.application")

Thank you for your response.
SBName is passed to the routine from the calling section, there is nothing wrong with the file name where this used.
My problem with the .xlsx file is that the spreadsheet (sheet 1) contains no data or formatting.

your suggested changed would return the code to original which produces the following error.

I had found the modification of the code that I had made on line that corrected this error for Access VBA.

The workbook is created! I was thinking this is something to do with copying the values and formats portion of the code or maybe saving after copying.

 

[Michael M]

myNewFileName = stPathName & SBName & " Monthly Charitable Activities Form.xlsx"

should be

myNewFileName = stPathName & "Monthly Charitable Activities Form.xlsx"

AND try

mySourceWB.Activate
    mySourceSheet.usedrange.Copy myDestWB.Range("A1")

 

[draziw]

myNewFileName = stPathName & SBName & " Monthly Charitable Activities Form.xlsx"

should be

myNewFileName = stPathName & "Monthly Charitable Activities Form.xlsx"

AND try

mySourceWB.Activate
    mySourceSheet.usedrange.Copy myDestWB.Range("A1")

Michael thank you for your response.
SBName stands for sub branch name and is prepended to the source file name to make a new dest file name, from what I can see this achieves what I require. Surely the source and destination file names need to be different?
your suggested change

mySourceWB.Activate
    mySourceSheet.usedrange.Copy myDestWB.Range("A1")

produces the following error.

 

[draziw]

As my spreadsheet is relatively small I decided to specify a cell range.

here is that portion of code modified.

    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1:J73").Select
    'ActiveSheet.Paste
    With Workbooks.Add.Sheets(1).Range("A1:J73")
   .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
   
   End With
   excelapp.CutCopyMode = False
'   Application.CutCopyMode = False

I still get the 2 problems as stated in my original post!