0

I have an audit that is done daily. I have been asked to save the audit points to a separate sheet for review by the mgmt. team. The code is below but when I run it I get an error: Script out of range.

Sub copy1()
Dim sheet2 As Worksheet
Dim sheet10 As Worksheet

Set sheet2 = Worksheets("sheet2")
Set sheet10 = Worksheets("sheet10")

sheet2.Range("a2:g10").Copy
sheet10.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).pastspecial xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub
Marcelo
  • 4,395
  • 1
  • 18
  • 30
HeliPilot17
  • 23
  • 1
  • 8
  • Thank you guys. This is a GREAT service to beginner VBA programmers. I used the code above and rewrote it several times because of a "Script out of range" error. The solution was quite simple, change "sheet2" to "DataEntry". All works fine. Again, thank you for you help. – HeliPilot17 Apr 07 '15 at 14:01

2 Answers2

0

It's difficult to say for sure, but it is likely that you are receiving the "Subscript out of range" error because your workbook does not have the worksheets named "Sheet2" and "Sheet10".

The following code copies data from Sheet2 and pastes the values of the data to the next available row on Sheet10.

Sub Copy2()

Dim DestinationStartingCell As Range
Dim SheetRowCount As Long

Worksheets("Sheet2").Range("A2:I29").Copy

SheetRowCount = Worksheets("Sheet10").Rows.Count '1048576 for Excel 2007 and later
Set DestinationStartingCell = Worksheets("Sheet10") _
 .Range("A" & SheetRowCount).End(xlUp).Offset(1, 0)
DestinationStartingCell.PasteSpecial xlPasteValues

End Sub
davidmneedham
  • 364
  • 1
  • 11
  • How can I make it dynamic in that everyday the range will be copied to the next row on sheet 10. – HeliPilot17 Apr 03 '15 at 14:50
  • See here: http://stackoverflow.com/questions/12497804/finding-first-blank-row-then-writing-to-it If you update your question body text to be more clear about what you need, I will also update my answer to match it. I see that your question title implies that you want the results copied to the next row. – davidmneedham Apr 03 '15 at 14:55
0

You could use this code

emptyrow=WorksheetFunction.CountA(Workbooks(<workbookname>).Sheets(10).Range("A:A"))+1

Workbooks(<workbookname>).Sheets(10).Cells(emptyrow,1).pastespecial xlPasteValues

to dynamically find the first empty row in Sheet10 to paste to.

USFBS
  • 279
  • 3
  • 12