Results 1 to 3 of 3

Thread: Copy active sheet range and paste into new workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy active sheet range and paste into new workbook

    I've seen various threads where many different variations of what I'm trying to do are explained but all seem to be just a tad bit different. What I'm trying to accomplish is write a script that will copy columns A:L in the active worksheet and paste into a new workbook. I would also like to receive the save as prompt with prefilled filename and file type(xlsx). After saving the new file, I would like to close it and be brought back to the previous active sheet. Not very far along as you see in the code below but its a start. Thanks

    Code:
    Sub AssessSave()
       Sheets("Overdue Assessments").Copy
       Application.Dialogs(xlDialogSaveAs).Show
       ActiveWorkbook.Close
    End Sub
    Last edited by VaYooper; Oct 22nd, 2019 at 11:15 AM.

  2. #2
    New Member
    Join Date
    Mar 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy active sheet range and paste into new workbook

    This is probably clunky but I've made some progress. My only hiccup is instead of this new workbook saving with the predefined name I would like a Save As prompt popup with this predefined information just in case they want to edit the name prior to it saving.
    Code:
    Sub AssessSave()
    Dim newWB As Workbook, currentWB As Workbook
    Dim newS As Worksheet, currentS As Worksheet
    
    'Copy the data you need
    Set currentWB = ThisWorkbook
    Set currentS = currentWB.Sheets("Overdue Assessments")
    currentS.Range("A:K").Select
    Selection.Copy
    
    'Create a new file that will receive the data
    Set newWB = Workbooks.Add
        With newWB
            Set newS = newWB.Sheets("Sheet1")
            newS.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Columns("K:K").Select
            Selection.NumberFormat = "[$-409]d-mmm-yy;@"
            Range("A1:K1").Select
            Selection.Font.Bold = True
        Range("A1:K1").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
        Range("A1").Select
            'Save in xlsx
            Application.DisplayAlerts = False
            .SaveAs fileName:="Overdue Assessments" & Format(Date, "MM-DD-YYYY"), FileFormat:=51
            Application.DisplayAlerts = True
        End With
       ActiveWorkbook.Close
        Range("A1").Select
    End Sub
    Last edited by VaYooper; Oct 22nd, 2019 at 02:36 PM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,217
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Copy active sheet range and paste into new workbook

    How about
    Code:
    Sub VaYooper()
        
        Sheets("Overdue Assessments").Copy
        With Application.FileDialog(2)
            .InitialFileName = "Afile.xlsx"
            If .Show = -1 Then .Execute
        End With
        ActiveWorkbook.Close
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •