CODEWITHSUNDEEP
vbaexcel
user2872317
user2872317

Reputation: 63

Excel VBA Copy a Range into a New Workbook

I am a newbie to Excel VBA.

I am trying to copy a range of data from worksheet output into a new excel workbook and save the new workbook with the the value in E3.

As a bonus, I would love to be able to also copy the data into wordpad and save as E3.xml

Here is the VBA I have thus far:

Sub CopyOutput()
Dim myname As String
mystring = E3
Dim myselection As Range

myselection = Sheets("Output").Columns("F").Select
Set NewBook = Workbooks.Add
    With NewBook
        .SaveAs Filename:="C:\Program Files\White Plume\Scenarios\" & myname & ".xls", FileFormat:= _
             xlsx, CreateBackup:=False
    End With
myselection.Paste
End Sub

Upvotes: 6

Views: 143716

Answers (1)

Lance Roberts
Lance Roberts

Reputation: 22842

Modify to suit your specifics, or make more generic as needed:

Private Sub CopyItOver()
  Set NewBook = Workbooks.Add
  Workbooks("Whatever.xlsx").Worksheets("output").Range("A1:K10").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
  NewBook.SaveAs FileName:=NewBook.Worksheets("Sheet1").Range("E3").Value
End Sub

Upvotes: 14

Related Questions