Using a list to copy and paste a range of data between worksheets - code error

[ngiqta]

Hello, I'm new here but have been using excel to manipulate data for many years.
I am trying to learn macros and VB code and have followed the instructions from an old thread (Thread: Copy data from one sheet to another based on Drop-Down box selection) but haven't been able to get it to work.

I have a list of locations on my 'Climate data' sheet and data on another sheet 'climate zones'. I am trying to get a range of data to be copied from 'climate zones' to the 'climate data' sheet depending on the location chosen from the drop down list. When i run the code i get a 'Run-time error 9 Subscript out of range' - the Target.value recognizes the change in the list location but keeps giving me an error. I have gone through the code and checked that all the ranges are correct but obviously i'm missing something.


Please help
Thanks

 

[ngiqta]

Amendment to above,
I didn't mention i am using excell 2016 and Win 10.
the code i am using is

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Atherton"
Sheets("Climate zones").Range("I5:U36").Copy Sheets("Climate Data").Range("G10")
Case "Brisbane"
Sheets("Climate zones").Range("V5:AH36").Copy Sheets("Climate Data").Range("G10")
Case "Bundaberg"
Sheets("Climate zones").Range("AI5:AU36").Copy Sheets("Climate Data").Range("G10")
Case "Carins"
Sheets("Climate zones").Range("AV5:BH36").Copy Sheets("Climate Data").Range("G10")
Case "Cleveland"
Sheets("Climate zones").Range("BI5:BU36").Copy Sheets("Climate Data").Range("G10")
Case "Gatton"
Sheets("Climate zones").Range("BV5:CH36").Copy Sheets("Climate Data").Range("G10")
Case "Gold Coast"
Sheets("Climate zones").Range("CI5:CU36").Copy Sheets("Climate Data").Range("G10")
Case "Mackay"
Sheets("Climate zones").Range("CV5:DH36").Copy Sheets("Climate Data").Range("G10")
Case "Maryborough"
Sheets("Climate zones").Range("DI5:DU36").Copy Sheets("Climate Data").Range("G10")
Case "Nambour"
Sheets("Climate zones").Range("DV5:EH36").Copy Sheets("Climate Data").Range("G10")
Case "Rockhampton"
Sheets("Climate zones").Range("EI5:EU36").Copy Sheets("Climate Data").Range("G10")
Case "Toowoomba"
Sheets("Climate zones").Range("EV5:FH36").Copy Sheets("Climate Data").Range("G10")
Case "Townsville"
Sheets("Climate zones").Range("FI5:FU36").Copy Sheets("Climate Data").Range("G10")
End Select
End Sub

 

[My Aswer Is This]

I suspect you have your sheet names incorrect.
Do you have a sheet named:
Climate zones
and
Climate Data

Spelling must be exact.

Your script worked for me. Doing limited test.

 

[ngiqta]

I suspect you have your sheet names incorrect.
Do you have a sheet named:
Climate zones
and
Climate Data

Spelling must be exact.

Your script worked for me. Doing limited test.

Although there have been 2 people look at this code several times, i'm embarrassed to say you were right - the sheet names varied slightly Climatic data rather than Climate data.
A simple change to the code and it works.

Thank you for your time and help

Regard
ngiqta

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Although there have been 2 people look at this code several times, i'm embarrassed to say you were right - the sheet names varied slightly Climatic data rather than Climate data.
A simple change to the code and it works.

Thank you for your time and help

Regard
ngiqta

 

[Fazza]

This is a good example of why codenames are used. Basic idea is that instead of referring to worksheets("some name like Climate")
Use the worksheet's code name. And you can change these within the VBE (VB Editor), btw. So the codename may default to Sheet1 but you can change it to wksClimateData
Then in the code you use wksClimateData instead of worksheets("some name like Climate")
The code name won't change, whereas if a user renames the worksheet, the other approach doesn't work

Google will explain it better than I have. such as
https://www.wiseowl.co.uk/blog/s112/microsoft-excel-vba-worksheet-sheet-name-codename.htm
http://www.brainbell.com/tutorials/...me_To_Reference_Sheets_In_Excel_Workbooks.htm

 

[My Aswer Is This]

To make it easier for me I do things like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub
Dim CF As String ' Copy From
Dim CT As String  ' Copy To
CF = "Climate zones"
CT = "Climate Data"
Select Case Target.Value
Case "Atherton"
Sheets(CF).Range("I5:U36").Copy Sheets(CT).Range("G10")
Case "Brisbane"
Sheets(CF).Range("V5:AH36").Copy Sheets(CT).Range("G10")
Case "Bundaberg"
Sheets(CF).Range("AI5:AU36").Copy Sheets(CT).Range("G10")
Case "Carins"
Sheets(CF).Range("AV5:BH36").Copy Sheets(CT).Range("G10")
Case "Cleveland"
Sheets(CF).Range("BI5:BU36").Copy Sheets(CT).Range("G10")
Case "Gatton"
Sheets(CF).Range("BV5:CH36").Copy Sheets(CT).Range("G10")
Case "Gold Coast"
Sheets(CF).Range("CI5:CU36").Copy Sheets(CT).Range("G10")
Case "Mackay"
Sheets(CF).Range("CV5:DH36").Copy Sheets(CT).Range("G10")
Case "Maryborough"
Sheets(CF).Range("DI5:DU36").Copy Sheets(CT).Range("G10")
Case "Nambour"
Sheets(CF).Range("DV5:EH36").Copy Sheets(CT).Range("G10")
Case "Rockhampton"
Sheets(CF).Range("EI5:EU36").Copy Sheets(CT).Range("G10")
Case "Toowoomba"
Sheets(CF).Range("EV5:FH36").Copy Sheets(CT).Range("G10")
Case "Townsville"
Sheets(CF).Range("FI5:FU36").Copy Sheets(CT).Range("G10")
End Select
End Sub