CODEWITHSUNDEEP
excelvba
Giovanni Manconi
Giovanni Manconi

Reputation: 1

Error 9 "out of range" when copying Excel range

I'm trying to copy data from one workbook to another after a button click but the function range doesn't work and always returns

error 9 "out of range".

Sub Button1_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open "C:\Users\Username\Desktop\Allineamento\Data\MasterData.xlsx"

Workbooks("MasterData").Sheets(2).range(Cells(13, 2), Cells(800, 16)).Copy
Workbooks("ImportSheets").Sheets("Master Data").Cells(2, 1).PasteSpecial Paste.Value

Workbooks("MasterData").Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Upvotes: 0

Views: 92

Answers (1)

Vityata
Vityata

Reputation: 43585

With basic Excel functions, the Macro-recorder is really useful. This is the code, generated by copying and pasting some excel range:

Sub Macro1()
    Range("A1:E8").Select
    Selection.Copy
    Selection.PasteSpecial xlPasteValues
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("L5").Select
    Application.WindowState = xlNormal
    Range("B7").Select
End Sub

Although, the code is not efficient, it is quite useful to see the needed code for the copy-paste - Range.PasteSpecial xlPasteValues

Thus, in the question code, replace PasteSpecial Paste.Value with PasteSpecial xlPasteValues.

Upvotes: 1

Related Questions