Activating a file in a macro by referencing a cell in a workbook

**Mrs F** · 09-09-2013, 12:02 PM

Good afternoon All,

I am a macro dunce so would be grateful for any help on getting this to work! I have tried searching the t'interweb but can't find the answer I need!

I am trying to copy and paste from one spreadsheet to another (from lots of different pages) - have written that bit of code!

I have named the spreadsheet with the macro in and where the data is being pasted so that I can just refer to it as Supajournal when I moving between the 2 files using the following code.

Supajournal = ActiveWorkbook.Name

I want to activate the file I am copying from by referencing a cell in C8 on a page in the Supajournal - this file has to be open because I need to press F9 to update the links in it before I copy and paste.

As the file name will change regularly, I just want to update it on the spreadsheet and not in the macro. I have tried using

Dim strFName As String

strFName = Sheet1.Range("C13").Value
Workbooks.Activate Filename:=strFName

but this doesn't work!

I need to know how to activate this file!

How can I move between workbooks using the cell reference rather than the full file name?

Thank you,
Mrs F

**HaHoBe** · 09-09-2013, 12:07 PM

Hi, Mrs. F.

Dim strFName As String

strFName = Sheet1.Range("C13").Value
Workbooks(strFName).Activate

where strFName or cell should contain both the name and the extension of the workbook. But there is hardly a reason to activate a workbook in order to copy.

Ciao,
Holger

**Mrs F** · 09-09-2013, 12:27 PM

Hi Holger,

Thanks for this but when I try that code, I get a Run-Time error '9' Subscript out of range error message. If I hit debug it highlights the line

Workbooks(strFName).Activate

I need to move between the workbooks as I am copying selected cells from several different pages from one workbook to another and it has to be copied rather than linked!

Any ideas what I am doing wrong?

Thanks,
Mrs F

**HaHoBe** · 09-09-2013, 01:06 PM

Hi, Mrs F,

that´s why I stated that the name in the cell must look like MyCopyBook.xlsx.

I need to move between the workbooks

Be assured that you need not to move. That´s the way the macro recorder works but we all know that this code could be reworked.

You would need to adjust the names of the sheets for this as well as the ranges:

Sub sample()
Dim wbThis As Workbook
Dim wbThat As Workbook
Dim wsFrom As Worksheet
Dim wsTo As Worksheet

Set wbThis = ThisWorkbook
Set wsTo = wbThis.Sheets("Result")

On Error Resume Next
Set wbThat = Workbooks(wbThis.Sheets("Data").Range("C13").Value)
On Error GoTo 0
If wbThat Is Nothing Then
  MsgBox "Can´t find '" & wbThis.Sheets("Data").Range("C13").Value & "' within the workbooks of this instance!"
  Exit Sub
End If
Set wsFrom = wbThat.Sheets("Raw Data")

wsTo.Range("A4").Value = wsFrom.Range("A1").Value
wsTo.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = wsFrom.Range("B1").Value

Set wsFrom = Nothing
Set wbThat = Nothing
Set wsTo = Nothing
Set wbThis = Nothing
End Sub

No switching between the workbooks/sheets is necessary.

Ciao,
Holger

**Mrs F** · 09-10-2013, 04:30 AM

Hi Holger,

Thank you so much for your help!

Have got the macro to work using the long winded moving between workbooks method!

Will investigate the code you have given me when I get time (but will probably be back asking more daft questions) as I think it would speed up my macro considerably!

Thanks again,
Mrs F

**HaHoBe** · 09-10-2013, 04:50 AM

Hi, Mrs F,

if this has been solved please mark the thread so by going to the first post and choose the option via Thread Tools.

Thanks,
Holger

Activating a file in a macro by referencing a cell in a workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Activating a file in a macro by referencing a cell in a workbook

Re: Activating a file in a macro by referencing a cell in a workbook

Re: Activating a file in a macro by referencing a cell in a workbook

Re: Activating a file in a macro by referencing a cell in a workbook

Re: Activating a file in a macro by referencing a cell in a workbook

Re: Activating a file in a macro by referencing a cell in a workbook

Thread Information

Users Browsing this Thread

Similar Threads

Macro to save workbook by referencing cell in another workbook for file name and file path

Activating/De-Activating Comments or only if the Cell is Active.

how to extract the value of a named cell in another workbook without activating it

Activating a macro after a cell is updated

Activating cell in a file

Bookmarks

Bookmarks

Posting Permissions