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

John.McLaughlin

John.McLaughlin

Board Regular
Joined
Jul 19, 2011
Messages
169
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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).
 
Upvote 0
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/showthread.php?p=3008912#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 :)
 
Upvote 0
John.McLaughlin said:
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?
Click to expand...

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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
John.McLaughlin said:
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.
Click to expand...

This statement should be returning the last cell with data in Columns(V:X).
Rich (BB 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...

Rich (BB code): 
Set rLastRow = Range("V:X").Find(What:="*", LookIn:=xlValues,  _
    SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
 
Upvote 0
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 :)
 
Upvote 0
John.McLaughlin said:
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 :)
Click to expand...


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

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! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Paste to specific cell & keep row / column & width / height values
Replies
9
Views
372
HighAndWilder
H
D
  • Question
Form Control to open a window or form to add a list of numbers, perform a calculation and paste value to a selected cell.
Replies
0
Views
81
DaveCutting
D
D
  • Question
New member - awkward Excel issue with copy and paste
Replies
1
Views
61
mumps
mumps
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
186
Insurance Dave
I
S
  • Question
Copy/paste special values of Excel table based on cell value
Replies
2
Views
148
stacey_t
S

Forum statistics

Threads
1,214,400
Messages
6,119,284
Members
448,885
Latest member
LokiSonic

Share this page

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
Back
Top