VBA - replacing a workbook name with cell

[Fredek]

Hi, I would like to replace "EC_Rpt01Sep17 955.xlsm" with Cell N14 from Data tab in Credit Scoring.xlsm. Could someone give me a hint how to do this please?

Workbooks("Credit Scoring.xlsm").Worksheets("MGDataTbl").Range("A1:R323").Value = Workbooks[COLOR=#FF0000]("EC_Rpt01Sep17 955.xlsm")[/COLOR].Worksheets("MGDataTbl").Range("A1:R323").Value

 

[Craggs82]

""" & hints!

 

[Joe4]

Give this a try:

    Dim wkbkName As String
    Windows("Credit Scoring.xlsm").Activate
    wkbkName = Sheet("Data").Range("N14")
    Workbooks("Credit Scoring.xlsm").Worksheets("MGDataTbl").Range("A1:R323").Value = Workbooks(wkbkName).Worksheets("MGDataTbl").Range("A1:R323").Value

 

[Fredek]

Thank Joe, I am getting a compile error: Sub or Function not defined , any idea what I'm missing?

Apologies, I am new to this! Thank you for your help.

 

[Joe4]

Sorry, had a minor typo. Should be:

    Dim wkbkName As String
    Windows("Credit Scoring.xlsm").Activate
    wkbkName = Sheet[COLOR=#ff0000][B]s[/B][/COLOR]("Data").Range("N14")
    Workbooks("Credit Scoring.xlsm").Worksheets("MGDataTbl").Range("A1:R323").Value = Workbooks(wkbkName).Worksheets("MGDataTbl").Range("A1:R323").Value

 

[Fredek]

I had a go with the new code and I get; Run time error 9, script out of range error.

For some reason it doesn't like it.

 

[Joe4]

It worked fine for me.

I suspect one of the following:
- bad file name
- bad sheet name
- protected or merged cells in the range you are trying to copy

 

[Fredek]

It worked fine for me.

I suspect one of the following:
- bad file name
- bad sheet name
- protected or merged cells in the range you are trying to copy

Hi Joe, I played around with it and it worked! Not sure what I was doing wrong at the start! Thank you very much!

 

[Joe4]

You are welcome!