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]

You're welcome

 

[justlearning4]

You're welcome
[/QUOTE]
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
Sub MovePar()
     
    Sheets("Calendar").Range("D3:D" & Sheets("Calendar").Range("D" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("D3")
    End With
    
End Sub
Sub MoveIssuer()
    Sheets("Calendar").Range("E3:E" & Sheets("Calendar").Range("E" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("C2:C" & .Range("C" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("E3")
    End With

End Sub
Sub State()
    Sheets("Calendar").Range("F3:F" & Sheets("Calendar").Range("F" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("D2:D" & .Range("D" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("D3")
    End With
End Sub
Sub UltimateBorrower()
    Sheets("Calendar").Range("G3:G" & Sheets("Calendar").Range("G" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("E2:E" & .Range("E" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("G3")
    End With

End Sub
Sub Moody()
    Sheets("Calendar").Range("H3:H" & Sheets("Calendar").Range("H" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("F2:F" & .Range("F" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("H3")
    End With
End Sub
Sub SP()
    Sheets("Calendar").Range("I3:I" & Sheets("Calendar").Range("I" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("E2:E" & .Range("E" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("I3")
    End With
End Sub
Sub Fitch()
    Sheets("Calendar").Range("J3:J" & Sheets("Calendar").Range("J" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("F2:F" & .Range("F" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("J3")
    End With
End Sub
Sub Enhancement()
    Sheets("Calendar").Range("K3:K" & Sheets("Calendar").Range("K" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("G2:G" & .Range("G" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("K3")
    End With
End Sub
Sub TaxStatus()
    Sheets("Calendar").Range("L3:L" & Sheets("Calendar").Range("L" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("H2:H" & .Range("H" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("3")
    End With
End Sub
Sub LeadManager()
    Sheets("Calendar").Range("O3:O" & Sheets("Calendar").Range("O" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("K2:K" & .Range("K" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("O3")
    End With
End Sub
Sub IssuerType()
    Sheets("Calendar").Range("M3:M" & Sheets("Calendar").Range("M" & Rows.Count).End(xlUp).Row).ClearContents
 
 
    With Sheets("Insert CDRN Data")
        .Range("L2:L" & .Range("L" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("M3")
    End With

End Sub

I am sorry, but it looks like the code is not working. It seems to be working for some columns, but not the others. The goal is to take what's in the "Insert CDRN Data" sheet and directly copy it to the "Calendar". See the attached pictures for how the data came in.

 

[MARK858]

To start with you haven't got a column reference with the bold range below

    With Sheets("Insert CDRN Data")
        .Range("H2:H" & .Range("H" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("3")
    End With

 

[justlearning4]

To start with you haven't got a column reference with the bold range below

    With Sheets("Insert CDRN Data")
        .Range("H2:H" & .Range("H" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("3")
    End With

True, I noticed that when I ran through the code after you sent it - my apologies for not saying that.

So....just throwing an idea out here. Instead of trying to reinvent the wheel, is there a code that will run a query to import an excel file that is saved on a k drive into a pre-designed template(as in excel file)? It seems like that is something power query could be a good candidate for, but I am unsure on how to make it a repeatable process. Maybe a macro?

 

[justlearning4]

To start with you haven't got a column reference with the bold range below

    With Sheets("Insert CDRN Data")
        .Range("H2:H" & .Range("H" & Rows.Count).End(xlUp).Row).Copy Sheets("Calendar").Range("3")
    End With

The "Insert CDRN Data" sheet source file has some columns that I would like to remove because they are irrelevant to my project. For example, the attached picture is the source file.

 

[MARK858]

It seems like that is something power query could be a good candidate for, but I am unsure on how to make it a repeatable process. Maybe a macro?

Power Query/Get and Transform has it's own code (which I doubt is what you mean by a macro) and is created each time you run a query.

The "Insert CDRN Data" sheet source file has some columns that I would like to remove because they are irrelevant to my project. For example, the attached picture is the source file.

The questions are straying further away from (additional to) the original question and you have also started questioning using Power Query / Get and Transform rather than the VBA you were originally using.

I think the best thing you can do is step back from the project, think through exactly what you need and then decide what method is best for you to use to achieve this.

Once you have decided on this then start a new thread with what you need clearly written (if you want to go down the Power Query / Get and Transform route then post it in the Power Tools forum [link below])

Power Tools

Power BI, Power Pivot, Power Query (Get and Transform), Power View, and Power Map

www.mrexcel.com

 

[justlearning4]

Power Query/Get and Transform has it's own code (which I doubt is what you mean by a macro) and is created each time you run a query.

The questions are straying further away from (additional to) the original question and you have also started questioning using Power Query / Get and Transform rather than the VBA you were originally using.

I think the best thing you can do is step back from the project, think through exactly what you need and then decide what method is best for you to use to achieve this.

Once you have decided on this then start a new thread with what you need clearly written (if you want to go down the Power Query / Get and Transform route then post it in the Power Tools forum [link below])

Power Tools

Power BI, Power Pivot, Power Query (Get and Transform), Power View, and Power Map

www.mrexcel.com

You are right, I was getting to into the weeds with this and I apologize if I wasted your time. I have gone ahead and posted my project on the Power Tools thread with explicit detail. Thank you again for all your help, and have a great night/day (not sure where you are).