.PasteSpecial Values and Formats

[bvanscoy678]

Hi,

I am very close to finishing this whole project, but I need one last tweek. I recieved help last week for the below code. The only trouble is I want to keep the values and formatting of the original worksheet.

I tried:

owb.Worksheets(1).Range("A1").PasteSpecial 12

But that must be wrong. I read and played with a lot of different settings with no luck.

Thanks for any help. Brent

Sub Simpleo()
Dim i As Integer
Dim owb As Workbook
Dim ws As Worksheet
Set ws = Sheet2
 
'' code works perfect, just need to figure out the copy/paste values and formatting
 
    For i = 2 To Sheet1.Range("E" & Rows.Count).End(xlUp).Row
        Sheet2.[E3] = Sheet1.Range("E" & i) & "-#1"
        Set owb = Workbooks.Add
        ws.Columns("A:H").Copy
        owb.Worksheets(1).Range("A1").PasteSpecial 12
        'Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        owb.SaveAs "C:\TEST Fitness Results\" & ws.[E3] & ".xls", FileFormat:=56
        owb.Close False
  Next i
End Sub

 

[SirJB7]

Hi, bvanscoy678!

Your posted code issues a:
.PasteSpecial 12
which is the equivalent to a:
.PasteSpecial xlPasteValuesAndNumberFormats
(check the uploaded file, an image of the VBA built-in help for PasteSpecial method in my local language, but the values and names are the same as yours)

The previous version (commented line) was issuing a:
.PasteSpecial xlValues
i.e., only values, nothing more.

Now you're getting values and number formats copied, so what do you want to get? Check all the possible values for the 1st parameter of the PasteSpecial method, which is xlPasteType and analyze if any of them is suitable for your needs.

Regards!

 

[Smallman]

Brent

This post relates to this thread;

http://forum.chandoo.org/threads/co...-in-name-range-with-a-twist.12967/#post-76729

I am sure Chandoo operates the same way as other forums, as such the questions should have been continued in that thread, since the question above directly relates to the code I gave you. Last I heard on that thread you were going to post back on Monday.

Now when you want to push the values and formats you have to do it over 2 lines. It is a bit of a shame that Msoft have not combined these two into one command but them are the breaks.

The following should do what you want.

Sub Simpleo()
Dim i As Integer
Dim owb As Workbook
Dim ws As Worksheet
Set ws = Sheet2
  
    For i = 2 To Sheet1.Range("E" & Rows.Count).End(xlUp).Row
        Sheet2.[E3] = Sheet1.Range("E" & i) & "-#1"
        Set owb = Workbooks.Add
        ws.Columns("A:H").Copy
        [a1].PasteSpecial 12
        [a1].PasteSpecial -4122 ' From Jame's attachment
        owb.SaveAs "C:\TEST Fitness Results\" & [E3] & ".xls"
        owb.Close False
  Next i
End Sub

Take care

Smallman

 

[bvanscoy678]

My apologizes for the miss post. I thought, since I eliminated the need for the email, I should start a new thread. I did go back to look at the code, but I found I was able to email from a previous project after I created all of the workbooks with your code. As you can see, I did reference the post and I always post comments in my final code to the threads I received help. Again, I will make sure in the future I don't break protocol because I respect and appreciate the help I receive. I can see now how the .PasteSpecial -4122 works.

Thank you to both for providing the solution and example to my question.

 

[SirJB7]

Hi, bvanscoy678!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: BTW it's a good practice to use the built-in constants name instead of the numeric values so as to keep readability and make it easy further changes in the future, without having to go to the help topic, come back, and so on for each hardcoded value. Hence back to your 1st code posted, I'd use the notation of the commented line, xlPasteValuesAndNumberFormats instead of 12 (take care that they are different values, I'm just talking about the notation). Same applies for all cases.

 

[bvanscoy678]

Thank you and I will keep that in mind. I am still very new using VBA, so every project is a big step for me!

Thanks, Brent

 

[Smallman]

Brent

Just understand with coding much of it is personal preference. I would recommend you use what ever you feel comfortable with. It is a binary language after all and as you feel more comfortable with what each aspect does you will find you comfortably use the numbers which relate to each task. For Beginners and those not familar with code the text is probably best.

Smallman

 

[bvanscoy678]

Since I only work on a few projects like these and they are small, I post all my completed projects into Microsoft OneNote with comments and some thread postings to help me reuse the code in other projects. Thank you for the comments and help. It is greatly appreciated. Brent