Code help need

[Bobmn4]

I am trying to get my copied Data to paste in the very next box but the DEBUG stops on the following any advise to get it to drop down to the next blank box I cant set a specific box because the information being copy and pasted varies. this code literally worked for weeks and just stopped today

Range("A2:J1003").Select
Selection.Copy
Sheets("EOC").Select
Range("A1").Select

Selection.End(xlDown).Offset(1, 0).Select

ActiveSheet.Paste

 

[Bobmn4]

Should have.
On the EOC sheet do Ctrl End & then look at col A for that row & above

so I started from scratch

Back to the initial issue so this code has worked for months with out issue but literally today it errors to debug the highlighted
here is the full code that was working until this AM the work book does have AC- and always will so the clinical screening comment should not effect anything

'Find greens
Range("A1").Select
ActiveSheet.Range("$A$1:$J$1003").AutoFilter Field:=10, Criteria1:= _
"=*ac-**", Operator:=xlOr, Criteria2:="=*Clinical Screening for**"
Range("A1:J1003").Select

'Copy greens
Range("A2:J1003").Select
Selection.Copy

'Adding Greens below reds
Sheets("EOC").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

'Color Greens
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With

 

[Fluff]

If you do Ctrl End on the EOC sheet where does it take you?

 

[Bobmn4]

If you do Ctrl End on the EOC sheet where does it take you?

to J1097 but there is no information in the EOC sheet its blank

 

[Bobmn4]

I guess the real question is why did this work until today it was working fine yesterday doing exactly what was needed
in earlier lines of the code it pull about 36 lines into the EOC and colors them red then later in the code we are trying to add more data bout 400 lines worth right under the existing reds and it has been working fine with the Selection.End(xlDown).Offset(1, 0).Select until today

 

[Fluff]

If there is no data on the EOC sheet then using End(Xldown) will take you to row 1,048,576 (ie the very last row) & you are then trying to offset that 1 row down, which is impossible.
Is the data you are copying just formulae?

 

[Bobmn4]

If there is no data on the EOC sheet then using End(Xldown) will take you to row 1,048,576 (ie the very last row) & you are then trying to offset that 1 row down, which is impossible.
Is the data you are copying just formulae?

but there is Data from the previous reds placed in there so I use CTRL down arrow from A1 which drops to the bottom of the data set in column A then offsets 1 row to get to a blank space

so there is data in the EOC and data to paste

ActiveSheet.Range("$A$1:$J$1003").AutoFilter Field:=10, Criteria1:= _
"=*ac-**", Operator:=xlOr, Criteria2:="=*Clinical Screening for**"
Range("A1:J1003").Select

finds 400 lines

 

[Fluff]

but there is Data from the previous reds placed in there

Then why did you say

to J1097 but there is no information in the EOC sheet its blank

It is very difficult to help if you keep changing your story.

 

[Bobmn4]

I am going to try to re record the MACRO using the same offset and see if it fixes its self maybe something further up the stream isnt working but there is no reason that it works for month and

Then why did you say

It is very difficult to help if you keep changing your story.

apologies I was looking at the spreadsheet where the error took place I have 11 tabs and 3 of which populated since the MACRO didnt finish not all the data pulled thru

when I do the same thing step by step manually it pulls the information just fine but when I try to run the macro (ordered in the same way) I get the error in the last few lines

it stopped at the EOC tab and didn't populate the rest of them I am just going to re record and see if that fixes it there may have been a change in the DATA somewhere in the parent document that is causing the issue.

I will end this thread and start another one with a different question but if the rewrite works Ill post it back here

 

[Fluff]

Do you have any blank rows within your data?

 

[Fluff]

If not try

Sub Bobmn()
   Dim NxtRw As Long
   
   NxtRw = Sheets("EOC").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   With ActiveSheet
      .Range("A1:J1").AutoFilter 10, "=*ac-**", xlOr, "=*Clinical Screening for**"
      .AutoFilter.Range.Offset(1).Copy Sheets("EOC").Range("A" & NxtRw)
      .AutoFilterMode = False
   End With
   With Sheets("EOC")
      With .Range("A" & NxtRw, .Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .Color = 5287936
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
   End With
End Sub