VBA to Copy & paste into new worksheet

[excel2007uk]

Hi I am trying to create a button that can copy the activesheet range "A1:H28" from a workbook called Quotation 3 & paste to a new excel spreadsheet, then save the file name as K3 from worksheet "Quote" from workbook "Quotation 3" then save this in "C:\Users\" & username & "\Dropbox\QUOTES\"

I would like to keep the format & column widths if possible, can anyone help? I'm running into an error at .pasteSpecial however I belive their might be a few more issues with this...

Sub copyintonewworkbook()

ActiveWorkbook.ActiveSheet.Range("A1:H28").Copy


    Dim FName           As String
    Dim FPath           As String
    Dim username        As String
    
    username = Environ$("username")
    
Set NewBook = Workbooks.Add
    With NewBook
        .SaveAs
        
    FPath = "C:\Users\" & username & "\Dropbox\QUOTES\"
    FName = ActiveWorkbook.ActiveSheet.Range("K3").Text
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

    End With
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

 

[rlv01]

Maybe something like this.

Sub copyintonewworkbook()
    Dim FName As String
    Dim FPath As String
    Dim username As String
    Dim NewBook As Workbook

    username = Environ$("username")
    FPath = "C:\Users\" & username & "\Dropbox\QUOTES\"

    With ActiveWorkbook
        FName = .Worksheets("Quote").Range("K3").Text
        .ActiveSheet.Range("A1:H28").Copy
    End With

    Set NewBook = Workbooks.Add
    With NewBook
        .Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .SaveAs Filename:=FPath & "\" & FName
    End With
End Sub

 

[excel2007uk]

Hi Thanks for your help, I get a runtime error 1004 application-defined or object-defined error on the line "ThisWorkbook.SaveAs Filename:=FPath & "\" & FName" any idea why it would happen?

 

[rlv01]

One reason would be if FPath is not a valid directory . What is the full text of the 1004 runtime error?

 

[Fluff]

Try

.SaveAs Filename:=FPath & FName

You do not need the \ as it's part of the path