Copy data from one workbook to another workbook based on critera

Question

I tried to copy data from one workbook to another workbook based on some criteria. The macro is written in the destination workbook as below. However when I run the code, i get an "Runtime Error 9. Script out of range error". Can anyone help me take a look of the code? Thanks!!!!

Sub sbCopyRangeToAnotherSheetFromLastRow()
    Dim s1Sheet As Worksheet, s2Sheet As Worksheet
    Dim source As String
    Dim target As String
    Dim path As String
    Dim path1 As String
    Dim rngSource As Range
    Dim rngTargetStart As Range
    Dim rngTargetStart2 As Range
    Dim j As Long, k As Long, erow As Integer

    source = "PB Position"  'Source Tab Name
    path_source = "C:\Temp\MIS RISK REPORT.xlsm"
    target = "Input - Trailing 12 week" 'Target tab Name
    Application.EnableCancelKey = xlDisabled

    Set s1Sheet = Workbooks.Open(path_source).Sheets(source)
    Set s2Sheet = ThisWorkbook.Sheets(target)
    Set rngSource = Range(s1Sheet.Range("A8"), s1Sheet.Cells.SpecialCells(xlCellTypeLastCell))
    Set rngTargetStart = s2Sheet.Range("C" & Rows.Count).End(xlUp).Offset(1)

    k = rngSource.Rows.Count

    For j = 8 To k
        If (rngSource.Cells(j, "O") = "K1" Or rngSource.Cells(j, "O") = "K2" Or rngSource.Cells(j, "O") = "G2") And rngSource.Cells(j, "AH") <> 1 Then
            rngSource.Cells(j, "A").EntireRow.Select
            Selection.Copy
            Worksheets(target).Select
            erow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row
            ActiveSheet.Cells(erow, "C").Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            Application.CutCopyMode = False
        End If
    Next j
End Sub

Your `Set rngSource` row should read `s1Sheet.Range("A8", s1Sheet.Cells.SpecialCells(xlCellTypeLastCell))`, that may not be the problem here but it actually targets the range you want I think! — Wolfie, Dec 20 '16 at 15:18

score 0 · Answer 1 · edited May 23 '17 at 12:13

I was about to write a third comment, so I'll lump all my advice into an answer and hopefully this clean-up will fix your problems.

1) Your Set rngSource row should read

s1Sheet.Range("A8", s1Sheet.Cells.SpecialCells(xlCellTypeLastCell))

That may not be the problem here but it actually targets the range you want I think!

2) You should also avoid using Select (see this previous SO question). Instead, first calculate erow, then use

rngSource.Cells(j, "A").EntireRow.copy destination:= ActiveSheet.Cells(erow,"C")

Except that you can't paste an entire row into a cell in column C! It should actually be

rngSource.Cells(j, "A").EntireRow.copy destination:= ActiveSheet.Cells(erow,"A")

THIS may be where your out of range error is coming from

Copy data from one workbook to another workbook based on critera

1 Answers1