Run-time error '9' - please help my sinking ship (excel workbook)

[justlearning4]

Hello,

This is my first time posting, so I hope it goes well. I am having an issue with developing a VBA code that transfers all data from the 'Insert CDRN Data" sheet to exact columns in the 'Calendar' sheet. Every time I run the code, I get an error message that says "Subscript out of range" which is odd because the sheets I am referencing all exist, which you can see in the attachments. I would greatly appreciate if someone could help me with this as I am in the first week of learning how to code with VBA.

*For reference, the Subs below "MoveDates" work, but I am trying to develop a code that copies and pastes all data that is shown in the "Insert CDRN Data".

Thank you!

 

[MARK858]

Your sheet name looks like it is "Calendar " not "Calendar" i.e. a space at the end of the name.

 

[justlearning4]

Ahhhh, you have a very keen eye, thank you. I am now getting an error for my last line of code in the first sub

 

[MARK858]

Can you post your code in the thread using code tags please rather than images (Copy your code, paste it in the thread, select all the code and click the

icon in the reply window as we can't copy/paste the code from an image

 

[justlearning4]

Can you post your code in the thread using code tags please rather than images (Copy your code, paste it in the thread, select all the code and click the View attachment 78975 icon in the reply window as we can't copy/paste the code from an image

Sub MoveDates()

    Sheets("Calendar").Activate
    Range("C3", Range("C3").End(xlDown)).ClearContents
 
 
    Sheets("Insert CDRN Data").Activate
    Range("A2", Range("A2").End(xlDown)).Copy
 
 
    Sheets("Calendar").Activate
    Range("C3").PasteSpecial


End Sub

 

[MARK858]

What happens with the code below?

Sub MoveDates()

    Sheets("Calendar").Range("C3", Sheets("Calendar").Range("C3").End(xlDown)).ClearContents
 
 
    With Sheets("Insert CDRN Data")
    .Range("A2", .Range("A2").End(xlDown)).Copy Sheets("Calendar").Range("C3")
    End With

End Sub

 

[justlearning4]

Sub MoveDates()

    Sheets("Calendar").Activate
    Range("C3", Range("C3").End(xlDown)).ClearContents
 
 
    Sheets("Insert CDRN Data").Activate
    Range("A2", Range("A2").End(xlDown)).Copy
 
 
    Sheets("Calendar").Activate
    Range("C3").PasteSpecial


End Sub

What happens with the code below?

Sub MoveDates()

    Sheets("Calendar").Range("C3", Sheets("Calendar").Range("C3").End(xlDown)).ClearContents
 
 
    With Sheets("Insert CDRN Data")
    .Range("A2", .Range("A2").End(xlDown)).Copy Sheets("Calendar").Range("C3")
    End With

End Sub

Great! That worked perfectly. Do you know how to format the pasted data into lets say white and light grey alternating cell colors?

 

[justlearning4]

What happens with the code below?

Sub MoveDates()

    Sheets("Calendar").Range("C3", Sheets("Calendar").Range("C3").End(xlDown)).ClearContents
 
 
    With Sheets("Insert CDRN Data")
    .Range("A2", .Range("A2").End(xlDown)).Copy Sheets("Calendar").Range("C3")
    End With

End Sub

So, I am having a problem with this code now. The data that I am bringing in from the "Insert CDRN Data" sheet sometimes has missing cell values. So, when I run this code for the columns that have missing data, the code stops at the first cell with no data in it. Would you have an alternative code that breaks through the empty cells and continues to look for data to bring over until the absolute last row? For instance, I have attached pictures comparing the two sheets.

 

[MARK858]

Would you have an alternative code that breaks through the empty cells

Sub MoveDates()

    Sheets("Calendar").Range("C3:C" & Sheets("Calendar").Range("C" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("C3")
    End With

End Sub

Do you know how to format the pasted data into lets say white and light grey alternating cell colors

Select the cells in the column column > Home > Conditional Formatting > New Rule > click Use a formula to determine which cells to format >in the Format values where this formula is true box put the formula =

MOD(ROW(),2)=0

Click Format > In the Format Cells box, click Fill >Pick a color > click OK.

or

Turn the range into a Table and change the table style to suit

or

run the code below (adjust the colour to suit)

Sub alternatecolor()

    Dim x As Long

    For x = 3 To Sheets("Calendar").Range("C" & Rows.Count).End(xlUp).Row
        If x Mod 2 = 0 Then
            Sheets("Calendar").Cells(x, "C").Interior.Color = 15132390
        End If
    Next

End Sub

 

[justlearning4]

Sub MoveDates()

    Sheets("Calendar").Range("C3:C" & Sheets("Calendar").Range("C" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("C3")
    End With

End Sub

Select the cells in the column column > Home > Conditional Formatting > New Rule > click Use a formula to determine which cells to format >in the Format values where this formula is true box put the formula =

MOD(ROW(),2)=0

Click Format > In the Format Cells box, click Fill >Pick a color > click OK.

or

Turn the range into a Table and change the table style to suit

or

run the code below (adjust the colour to suit)

Sub alternatecolor()

    Dim x As Long

    For x = 3 To Sheets("Calendar").Range("C" & Rows.Count).End(xlUp).Row
        If x Mod 2 = 0 Then
            Sheets("Calendar").Cells(x, "C").Interior.Color = 15132390
        End If
    Next

End Sub

Holllllyyyyyyyyy heck, you are good. I admire your skills, and I appreciate your help!