Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Can I "Paste Special w/values & number formatting" AND "keep source column width"

This is a discussion on Can I "Paste Special w/values & number formatting" AND "keep source column width" within the Excel Questions forums, part of the Question Forums category; Hello, I am trying to create a simple macro to copy a range to {Workbooks.Add} then {ActiveSheet.Paste}. Can I combine ...

  1. #1
    Board Regular John.McLaughlin's Avatar
    Join Date
    Jul 2011
    Location
    Kansas City
    Posts
    57

    Default Can I "Paste Special w/values & number formatting" AND "keep source column width"

    Hello,

    I am trying to create a simple macro to copy a range to {Workbooks.Add} then {ActiveSheet.Paste}.

    Can I combine the Paste special w/values and number formats AND the keep source column widths together?

    Thank you in advance.

    JM

  2. #2
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,781

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    Hi JM,

    If you want to PasteSpecial those Properties, you'll need to use two PasteSpecial statements.

    Code:
    Sub CopyRange()
        Sheets("Sheet1").Range("A1:J10").Copy
        With Sheets("Sheet2").Range("A1")
            .PasteSpecial (xlPasteValuesAndNumberFormats)
            .PasteSpecial (xlPasteColumnWidths)
        End With
    End Sub
    If want to copy an entire sheet to a new workbook, you might consider simply...
    Code:
    Sub CopySheetToNewWorkbook()
        Sheets("Sheet1").Copy
    End Sub
    (This will copy All properties. If you only want Values, NumberFormats, and ColumnWidths you could use the other technique).
    Using Excel 2010, 2013

  3. #3
    Board Regular John.McLaughlin's Avatar
    Join Date
    Jul 2011
    Location
    Kansas City
    Posts
    57

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    That's awesome, thank you very much for your help. I easily added your tip to my macro and now my results are more readable.

    This is how far I've gotten on my macro, with the help of Firefly2012 at another thread. http://www.mrexcel.com/forum/showthr...12#post3008912

    Currently I am trying to learn how to add "static" text below the last row in my newly created list. It actually needs to start at the second blank row for page formatting purposes, however, the last row number of data is always different?

    Code:
    Sub copylist()
    '
    ' textcopy Macro
    ' use customername and city for filename
    '
    
    Sheets("ORDERS").Select
    Dim FP As String, FN As String, FJ As String
    FP = "j:\copyorders\" ' file path of where files will be saved
    FN = Range("A2").Value ' file name from cell value - customer name
    FJ = Range("W2").Value ' filename from cell value - city
    
    Dim rLastRow As Range
    Set rLastRow = Range("V:X").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
    	If Not rLastRow Is Nothing Then
    		If rLastRow.Row >= 5 Then
    			Range("V5:X" & rLastRow.Row).Copy
    			Else
    			MsgBox "Doesn't appear to be much data on sheet!"
    		End If
    	Else
    	MsgBox "No data on sheet!"
    	End If
    
        Workbooks.Add
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "COMPANY NAME"
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "ORDERS"
        Range("A4").Select
        ActiveCell.FormulaR1C1 = "Required"
        Range("A5").Select
    	Application.Goto Reference:="R5C1"
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    	
    		
        Application.CutCopyMode = False
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:= _
            FP & FN & FJ & ".txt", FileFormat:=xlTextMSDOS, _
            CreateBackup:=False
           
    '
    End Sub

    Thanks again for your help. I create and test little macro functions one at a time, and after they work, add them to a larger macro. It might not be pretty or fast, but it gets the job done and I am learning more each day

  4. #4
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,781

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    Quote Originally Posted by John.McLaughlin View Post
    Currently I am trying to learn how to add "static" text below the last row in my newly created list. It actually needs to start at the second blank row for page formatting purposes, however, the last row number of data is always different?
    Two options to do that are:
    1. Use the Find function to find the Last Row of data as you did earlier in your Procedure. (your static text will begin at Row: rLastRow.Row+2)
    Since you've Pasted in Cell A5, the statement would search A:C instead of V:X.

    2. As a shortcut, you know that the Last Row of data in you new sheet will be the same as the Last Row in the source sheet (because both ranges started at Row 5). So you could forgo the Find function and just start your static text at Row: rLastRow.Row+2.
    Using Excel 2010, 2013

  5. #5
    Board Regular John.McLaughlin's Avatar
    Join Date
    Jul 2011
    Location
    Kansas City
    Posts
    57

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    Thanks again, JS411.

    I am trying to implement the shortcut, but (I think) my rLastRow.Row + 2 syntax (or placement) is wrong?

    The text is pasting into the sheet ok, but clear down at row 206. The source sheet has Labels down to 204, but the data stops at row 28 in my test file. I have been trying to fix my error thinking the 206 is the 204 + 2?

    Can you see where I am going wrong? Thank you in advance.

    Code:
    Sub copylist16()
    '
    ' textcopy Macro
    ' use customername and city for filename
    ' ok - has page title added first and returns you to cell a5
    ' 12 - save workbook as excel spreadshhet
    
    
    Sheets("ORDERS").Select
    Dim FP As String, FN As String, FJ As String
    FP = "j:\copycutlist\" ' file path of where files will be saved
    FN = Range("A2").Value ' file name from cell value - shipper name
    FJ = Range("W2").Value ' filename from cell value - tops
    
    Dim rLastRow As Range
    
    Set rLastRow = Range("V:X").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
    
    If Not rLastRow Is Nothing Then
      If rLastRow.Row >= 5 Then
         Range("V5:X" & rLastRow.Row).Copy
      Else
         MsgBox "Doesn't appear to be much data on sheet!"
      End If
    Else
      MsgBox "No data on sheet!"
    End If
    
        Workbooks.Add
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "COMPANY NAME"
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "ORDERS"
        Range("A4").Select
        ActiveCell.FormulaR1C1 = "Required"
        
        Application.Goto Reference:="R5C1"
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
         Range("A" & rLastRow.Row + 2).Select
        
        
        ActiveCell.FormulaR1C1 = "TITLE"
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(1, -2).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        Application.CutCopyMode = False
        Application.DisplayAlerts = False
        
    
    
        Range("A5").Select
        ActiveWorkbook.SaveAs Filename:= _
            FP & FN & FJ & ".txt", FileFormat:=xlTextMSDOS, _
            CreateBackup:=False
        ActiveWorkbook.SaveAs Filename:= _
            FP & FN & FJ & ".xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
     
           
    '
    End Sub
    Last edited by John.McLaughlin; Jan 22nd, 2012 at 07:30 PM.

  6. #6
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,781

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    John, What column has the labels, and what column should be used to find the last row of data?
    Using Excel 2010, 2013

  7. #7
    Board Regular John.McLaughlin's Avatar
    Join Date
    Jul 2011
    Location
    Kansas City
    Posts
    57

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    The source sheet has labels in columns U. The data for the report is drawn from columns V to X.

    My destination sheet places the data into columns A, B & C.

    And when I say labels I mean, Source Column U = 1, 2, 3, 4 etc. They are just simple Row "titles". Source Column A also has 1, 2, 3, etc.

  8. #8
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,781

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    Quote Originally Posted by John.McLaughlin View Post
    The source sheet has labels in columns U. The data for the report is drawn from columns V to X.

    My destination sheet places the data into columns A, B & C.

    And when I say labels I mean, Source Column U = 1, 2, 3, 4 etc. They are just simple Row "titles". Source Column A also has 1, 2, 3, etc.
    This statement should be returning the last cell with data in Columns(V:X).
    Code:
    Set rLastRow = Range("V:X").Find(What:="*",   _
        SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
    If you're getting the last row of data in Column U, then something else is going on.

    Do you have formulas that extend down to Row 204 for any of Columns V:X ?
    Those could be considered data in the find statement above.

    If you want to find the last row with data Values that aren't Null (blank), you could modify your find to...

    Code:
    Set rLastRow = Range("V:X").Find(What:="*", LookIn:=xlValues,  _
        SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
    Using Excel 2010, 2013

  9. #9
    Board Regular John.McLaughlin's Avatar
    Join Date
    Jul 2011
    Location
    Kansas City
    Posts
    57

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    Thank you, JS411. LookIn:=xlValues was the solution.

    Yes, there are formulas in the range. I display "" if the condition is false, and it never occurred to me "*" would read it as non-blank... geez, I have sooo much to learn.

    Another weekend lost to my desk. It's 20 in Kansas City, thank you very much for sacrificing time in your warmer weather to help me

  10. #10
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,781

    Default Re: Can I "Paste Special w/values & number formatting" AND "keep source column width"

    Quote Originally Posted by John.McLaughlin View Post
    Thank you, JS411. LookIn:=xlValues was the solution.

    Yes, there are formulas in the range. I display "" if the condition is false, and it never occurred to me "*" would read it as non-blank... geez, I have sooo much to learn.

    Another weekend lost to my desk. It's 20 in Kansas City, thank you very much for sacrificing time in your warmer weather to help me

    John, You're welcome to visit San Diego anytime you need to thaw out.

    If I could offer you one more suggestion, your coding will be better if you eliminate the use of Select...Selection...ActiveCell
    and instead directly reference the Cells or Ranges without Selecting them.

    For example this part of your code....
    Code:
        Workbooks.Add
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "COMPANY NAME"
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "ORDERS"
        Range("A4").Select
        ActiveCell.FormulaR1C1 = "Required"
        
        Application.Goto Reference:="R5C1"
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
         Range("A" & rLastRow.Row + 2).Select
            
        ActiveCell.FormulaR1C1 = "TITLE"
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(1, -2).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "TEXT"
    Could be replaced by....
    Code:
        Workbooks.Add
        Range("A1") = "COMPANY NAME"
        Range("A2") = "ORDERS"
        Range("A4") = "Required"
           
        With Range("A5")
            .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteColumnWidths, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
        End With
        
        With Range("A" & rLastRow.Row + 2)
            .Value = "TITLE_A"
            .Offset(0, 1) = "TITLE_B"
            .Offset(0, 2) = "TITLE_C"
            .Offset(1) = "TEXT_A"
            .Offset(1, 1) = "TEXT_B"
            .Offset(1, 2) = "TEXT_C"
        End With
    Good luck!
    Using Excel 2010, 2013

Page 1 of 2 12 LastLast

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
  •  


DMCA.com