Paste columns, rows or cells

[shimuno]

Hello,

I am trying to design a macro allowing the user to paste data in an Excel file that he has copied in another Excel file. The data copied can be one or several columns, rows or cells.

The problem is that I am unable to find the proper paste instruction. I've been working on that for hours!

Here is the code. An error happens here: Sheets("TempArrow").Cells Paste:=xlPasteValues

If you have any idea... Many thanks for your help!

[VBA]Sub Delete_Paste_Arrow()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlSemiautomatic

'Creation of a temporary sheet

Sheets.Add.Name = "TempArrow"

'Pasting the data on the temporary sheet

Sheets("TempArrow").Cells.PasteSpecial Paste:=xlPasteValues
Sheets("TempArrow").Cells.PasteSpecial Paste:=xlPasteFormats

'Erasing the existing data on the sheet named Arrow

Sheets("Arrow").Rows("5:36000").Clear

'Copying the data from the sheet TempArrow to the sheet Arrow

With Sheets("TempArrow")
.Range(.Cells(1, 1), .Cells(10000, 50)).Copy Sheets("Arrow").Cells(5, 1)
End With

'Delete the sheet TempArrow

Sheets("TempArrow").Delete

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub[/VBA]

[Kenneth Hobs]

You did not Copy anything and you did not set a cell or cells to paste to.

[VBA]Sub Delete_Paste_Arrow()
Dim r As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlSemiautomatic

Set r = ActiveSheet.UsedRange
r.Copy

'Creation of a temporary sheet
Sheets.Add.Name = "TempArrow"

'Pasting the data on the temporary sheet
'Sheets("TempArrow").Cells(1, 1).Resize(r.Rows.Count, r.Columns.Count).PasteSpecial Paste:=xlPasteValues
'Sheets("TempArrow").Cells(1, 1).Resize(r.Rows.Count, r.Columns.Count).PasteSpecial Paste:=xlPasteFormats
Sheets("TempArrow").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Sheets("TempArrow").Cells(1, 1).PasteSpecial Paste:=xlPasteFormats

'Erasing the existing data on the sheet named Arrow
Sheets("Arrow").Rows("5:36000").Clear

'Copying the data from the sheet TempArrow to the sheet Arrow

With Sheets("TempArrow")
.Range(.Cells(1, 1), .Cells(10000, 50)).Copy Sheets("Arrow").Cells(5, 1)
End With

'Delete the sheet TempArrow

Sheets("TempArrow").Delete

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Application.CutCopyMode = False
Range("A1").Select
End Sub[/VBA]