Paste Special as Text

[Drivingman]

Hi All

Hoping you can help a VBA beginner.

I have data that I am copying from EXCEL 16, and pasting into EXCEL 07. Unfortunately no way around this backward paste. Customer still on old version.

I can manually paste the data in, but repetitive as have 5 days of data to paste.
When I use the following macro, it pastes it in as an image and not into the cells directly.
Sub TextBox1_Click()
'
' TextBox1_Click Macro
' Paste into Monday
'
'

With Sheets("Control")
.Range("d8").PasteSpecial xlPasteAll
End With
End Sub

When I have tried the following amended macro it generates an error.
Sub TextBox1_Click()
'
' TextBox1_Click Macro
' Paste into Monday
'
'
With Sheets("Control")
.Range("d8").PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
End With
End Sub


The data should be pasted into cells D8:L57 on sheet Control.
Once I have that sorted I then need to amend to paste Tuesday into D61:L110, and so on through to Friday.
On my front sheet I have 5 text boxes which I am hoping to have the 5 macros assigned to.
Hope this all makes sense.

TYIA.

 

[rlv01]

A couple of comments.

1) Normally to paste something you need to copy something first. I don't see any "copy" statements in your code. What is being copied?
2) You've mentioned that an error was generated. There are all kinds of errors. When excel generates an error, it is important to be very specific about exactly which error and it's error message when posting about it, because that is information that will provide a clue as to what is going on.

 

[Drivingman]

Hi rlv01

1 - The data to be pasted is copied from another excel 2016 spreadsheet that is generated from Injixo - our workforce management software. Each day is produced separately and could be named Book1 - 5. Each spreadsheet has a sheet named OnlineCockpit Generated Sheet.
I have been manually copying cells B32:J81 from this sheet. Because I would be copying from 5 different spreadsheets I thought it better to leave the copy out and do that manually.

2 - the error is "Run-time error '1004': Application-defined or object-defined error"

Hope this helps.

 

[rlv01]

With Sheets("Control")
    .Range("d8").PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
End With

With the above, you are using the Range PasteSpecial method. The Range PasteSpecial method has no format, link, or DisplayAsIcon parameters. Hence the runtime 1004 error. The Format, link, or DisplayAsIcon parameters *DO* exist for the Worksheet PasteSpecial method. The simplest solution might be to just paste values

With Sheets("Control")
    .Range("d8").PasteSpecial xlPasteAll
End With

But you can use the worksheet PasteSpecial method so long as you select the destination range first

Sub TextBox1_Click()
'
' TextBox1_Click Macro
' Paste into Monday
'
'
With Sheets("Control")
    .Range("d8").select
    .PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End With
End Sub

Note that the data you paste using worksheet.pastespecial has to be in the windows clipboard.

 

[Drivingman]

HI Rlv

All sorted now. Thank you very much!!!!!