Paste Values vba error (MS Excel 2007)

Your code is very confusing. You have named one cell as both a variant and a string. Additionally, you have activated multiple sheets.

How about telling us exactly what you wish to happen and give us some clear examples so that we can provide you with some clean code that will do exactly what you wish and help you to understand how VBA works. A sample worksheet uploaded with a before and after example will be very helpful.

Cross-posted without a link @
http://www.mrexcel.com/forum/excel-q...-headache.html

I'm confused as well. here's a guess.

Sub TextFile_Annual_Summary()
' TextedFile Macro
' Copy Annual Summary into new file and c/p value everything
'

    Application.ScreenUpdating = False

    'Copy sheet as new workbook
    Sheets("Annual Summary").Copy
    
    'Rename copied sheet
    ActiveSheet.Name = ThisWorkbook.Sheets("Ref").Range("N10").Text & " Annual Summary"
    
    'Convert formulas to values and keep formats
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
    ActiveWindow.DisplayOutline = False
    Range("D7").Select
    
    Application.ScreenUpdating = True
    
End Sub

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

First of all just to be clear it isn't the cross-posting that is the issue per se, it is just that you didn't post a link to the cross-post.

Anyway I will post the below as you seem to understand the issue above now and I posted a link to your cross-post in post #3 (as long as it is the only site you have cross-posted the question on)...

Can you attach a anonymized copy of your workbook (see the excerpts in the quote below for how) as I can't reproduce your issue currently with how I think your workbook is set up.

Dear Users,

This message is to inform you that due to some technical issues the “Paper Clip" (To Attach Files) option that you see while posting new threads on Excel Forum is not operational right now. Our tech team is looking into the matter but as of now we would suggest you to kindly scroll down your Message box/Text Box a bit and then under the Additional Options tab, use the "Manage Attachments" option instead to attach your files with your queries while posting new threads......

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen

Btw I think that both the codes below are doing the same as the last code you posted (post #8), can you check that I am right.

Sub Annual_SummarySummary_Text3()

    ' CopyPaste_P&Ls Macro

    Dim curWorkbook As Workbook
    Set curWorkbook = ActiveWorkbook

    Application.ScreenUpdating = False
    Sheets("Annual Summary").Copy
    
    With ActiveSheet.Cells
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
     
    Application.CutCopyMode = False
    'Sheets("Annual Summary").Range("A6").Select
    'I don't see the purpose of the line above which is why I commented it out
    Application.ScreenUpdating = True

    Application.Goto curWorkbook.Sheets("User Input&Pull").Range("H3")

End Sub

Sub Annual_SummarySummary_Text2()

    ' CopyPaste_P&Ls Macro

    Dim curWorkbook As Workbook
    Set curWorkbook = ActiveWorkbook

    Application.ScreenUpdating = False
    Sheets("Annual Summary").Copy

    With ActiveSheet.Cells
        .Value = .Value
    End With

    'Sheets("Annual Summary").Range("A6").Select
    'I don't see the purpose of the line above which is why I commented it out
    Application.ScreenUpdating = True

    Application.Goto curWorkbook.Sheets("User Input&Pull").Range("H3")

End Sub

I'm guessing you're using formulas on your original Annual Summary sheet that break down when the sheet is copied as a new workbook. I can't be more specific without having more details. Try this code. It copies the values from the original sheet.

Sub TextFile_Annual_Summary()
' TextedFile Macro
' Copy Annual Summary into new file and c/p value everything
'
    
    Application.ScreenUpdating = False
    
    'Copy sheet as new workbook
    Sheets("Annual Summary").Copy
    
    'Rename copied sheet
    ActiveSheet.Name = ThisWorkbook.Sheets("Ref").Range("N10").Text & " Annual Summary"
    
    'Convert formulas to values and keep formats
    ActiveSheet.UsedRange.Value = ThisWorkbook.Sheets("Annual Summary").UsedRange.Value
    
    ActiveWindow.DisplayOutline = False
    Range("D7").Select
    
    Application.ScreenUpdating = True
    
End Sub