VBA to Copy & paste into new worksheet

excel2007uk

New Member
Joined
Jul 13, 2018
Messages
13
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...

VBA Code:
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Maybe something like this.
VBA Code:
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

New Member
Joined
Jul 13, 2018
Messages
13
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

Well-known Member
Joined
May 16, 2017
Messages
771
One reason would be if FPath is not a valid directory . What is the full text of the 1004 runtime error?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,799
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
.SaveAs Filename:=FPath & FName
You do not need the \ as it's part of the path
 

Watch MrExcel Video

Forum statistics

Threads
1,128,157
Messages
5,629,027
Members
416,359
Latest member
Juena

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top