Can you see an issue with this code please

[ipbr21054]

Hi,
I have a code which works well & correct,it then calls the macro below.

Sub sheettolist()'
' sheettolist Macro
Sheets("HONDA SHEET").Range("A21:G21").Interior.ColorIndex = 6
Sheets("HONDA SHEET").Range("A21:G21").Copy
Sheets("HONDA LIST").Range("A4").Insert Shift:=xlDown
Application.CutCopyMode = False
Sheets("HONDA LIST").Range("A4").Characters(Start:=10, Length:=1).Font.ColorIndex = 3
Sheets("HONDA LIST").Range("A4").Select
ActiveWorkbook.Save
Sheets("HONDA SHEET").Range("A13").Select
End Sub

This should copy the data from HONDA SHEET to HONDA LIST
Select cell A4
Then return back to HONDA SHEET
Where cell A13 is then selected.

On each sheet i have a working code where once you select a cell that row will be Blue & the selected cell Green.
So in the code above i would like cell A4 slected on HONDA LIST so when i then return to it that row & cell is colored as mentioned.

With Sheets("HONDA LIST").Range("A4").Select in the code it returns a message,
Run time error 1004
Select class range of methos class failed.

So if i remove Sheets("HONDA LIST").Range("A4").Select,
I receive no errors.
The cell A4 on HONDA LIST is selected BUT the row/cell color is not updated & the next row down has the row/cell colored.

Many thanks

 

[NoSparks]

You can't select a cell on a sheet that isn't active.
To verify, try doing it manually.

 

[ipbr21054]

Here is a short video clip to show you what i mean.

https://www.theremotedoctor.co.uk/forums/EXCELA4.mp4

 

[ipbr21054]

OK,
I posted my last message then your post appeared.

The below seems to now work without errors but i see the cursor going around a few times & sluggish.

Sub sheettolist()'
' sheettolist Macro
Sheets("HONDA SHEET").Range("A21:G21").Interior.ColorIndex = 6
Sheets("HONDA SHEET").Range("A21:G21").Copy
Sheets("HONDA LIST").Range("A4").Insert Shift:=xlDown
Application.CutCopyMode = False
Sheets("HONDA LIST").Range("A4").Characters(Start:=10, Length:=1).Font.ColorIndex = 3
ActiveWorkbook.Sheets("HONDA LIST").Activate
ActiveSheet.Range("A4").Select
ActiveWorkbook.Save
ActiveWorkbook.Sheets("HONDA SHEET").Activate
ActiveSheet.Range("A13").Select
ActiveWorkbook.Save
End Sub

 

[rlv01]

Try turning off screen updating while your macro runs, and only save the activeworkbook once, not twice as you are doing now.

Sub sheettolist()                                    '
' sheettolist Macro
    Application.ScreenUpdating = False
    With ActiveWorkbook
        .Sheets("HONDA SHEET").Range("A21:G21").Interior.ColorIndex = 6
        .Sheets("HONDA SHEET").Range("A21:G21").Copy
        .Sheets("HONDA LIST").Range("A4").Insert Shift:=xlDown
        Application.CutCopyMode = False
        .Sheets("HONDA LIST").Range("A4").Characters(Start:=10, length:=1).Font.ColorIndex = 3
        .Sheets("HONDA LIST").Activate
        ActiveSheet.Range("A4").Select
        .Sheets("HONDA SHEET").Activate
        ActiveSheet.Range("A13").Select
        .Save
    End With
    Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

Hi,
I tried the above but still the cell / row colors do not change

 

[NoSparks]

Is the code that controls the cell/row colors ever being triggered during the running of this macro ?
Run the macro using the F8 key to step through the code one line at a time and see where it goes ?
The "Honda List" Worksheet events
_Activate, _Change and _SelectionChange, which likely sets the colors, will all be getting triggered unless events are being set false somewhere.

 

[ipbr21054]

Not quite sure if im doing this correct as per post #7
I select to the left of the top line of code & now i see a red dot.
I press F8 and see a line of code now yellow.
I also see a yellow arrow on the left.

I keep pressing F8 and then i see it reach the line code code for the color change,this is what i see change yellow.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
THEN
If Target.Cells.Count > 1 Then
THEN
Exit Sub

It then goes to another code on the page & starts to work through it.

 

[rlv01]

What problem are you trying to solve? In post #4 you said you had code that worked and that you were trying to solve a 'sluggishness' problem.

 

[NoSparks]

try making the sheet active before pasting to it.