-
Paste columns, rows or cells
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]
-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules