Macro to paste file name between workbooks

[ExcelGlyn]

Hi all, first post, here goes, hope someone can help urgently . . .

I have just started a new job, and have inherited some files that need updating that contain macros. Most I understand fine, and work no problem, but I have an issue with one pair of macros that should allow me to copy data from a worksheet ion one workbook, and capture the file name of that workbook, and then paste the data into another open workbook, with the filename (of the file where the data has come from) being pasted into the next available column cell at the end of each row of data pasted in.

The problem is that although the macro works to capture the data and the file name, and will allow you to paste it into a different worksheet on the same file, as soon as I switch to the other workbook and run the past macro, it pastes the data fine, but the file name is not added alongside each row. Instead it puts blank cells there. I really need to find a way of adding the file name from the one file to the other file when pasting in the data. Please help asap !!

Here are the two macro scripts :-

Sub copyForReturn()
'unfilter to be safe
On Error GoTo 7
Worksheets("Toolkit").ShowAllData
7:

'copy data from toolkit output tab to return tab
Range("completeOutputRange").Copy

'get the file name for pasting
fileName = ActiveWorkbook.Name

End Sub



Sub pasteReturn()
'put copied returns data into the bottom of the returns tab

'fetch data last row
LastRow = Sheets("Returns").Cells(Sheets("Returns").Rows.Count, "A").End(xlUp).Row + 1

'paste the data in
Range("A" & LastRow).Select

'if error (ie no selction copied) tank out of the sub
On Error GoTo 5

'paste clipboard
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

'fetch data last row new
LastRow2 = Sheets("Returns").Cells(Sheets("Returns").Rows.Count, "A").End(xlUp).Row

'add toolkit name to returns
For i = LastRow To LastRow2
Range("AE" & i) = fileName
Next I

5:
End Sub

So it's this last bit of macro, the 'add toolkit name to returns, that doesn't work when you switch files.

Please, please, can anyone come up with a solution to resolve this? I've tried numerous things so far, and have searched the web for an appropriate post, but haven't been able to find anything even close. So I'd really appreciate your help. I need to get this up and running really urgently, so please, if you can, please help me with your solutions.

Many thanks
Glyn

 

[RoryA]

Welcome to the forum.

Where is the filename variable declared?

 

[ExcelGlyn]

Welcome to the forum.

Where is the filename variable declared?

Hi Rory
It picks up the name of file 1 from the last line of the first listed macro script, fileName = activeWorkbook.name
as part of the copy macro activated in file 1.
If you stay in that same file (file 1) it pastes onto another worksheet just fine, but annoyingly it drops the active workbook file name when I change to the other file (file 2). Need to know what to change in the macro to keep the name as fileName when I switch to file 2 where it actually needs to be pasted. Hope this helps explain.
Glyn

 

[njimack]

Is there a reason that they're 2 separate subroutines rather than one piece of code?

 

[ExcelGlyn]

Only because they are actions taken in two different spreadsheets; I.e. The copy macro button is used in the first file and the paste one in the second file. But if there's a way of doing it in one subroutine that will work, that's fine. However, one complication is that I don't know in advance what the names of the files are, as this is a data job where a big load of data is split into a few smaller bits, and then other contacts have to then split their data into further smaller files, and it is these further smaller files that the other contact has to consolidate back into one file. So it can be one subroutine if that's easier, but it has to work with the two open files and needs to be able to pick up the file name from the one with the data to copy and paste the data with the added file name on the end of each row, not have the file name specified already in the actual subroutine.
Thanks for your interest in helping, hope someone can come up with a solution.
Cheers
Glyn

 

[RoryA]

I asked where it is declared, not where you assign it a value. There should be a statement that looks like Dim filename As String somewhere - is there, and if so, where?

 

[ExcelGlyn]

No, there doesn't seem to be anything like that anywhere from what I can see. How does that work? I just tried adding the line "Dim filename as String" before the line that assigns the value, and when I run the macro now it doesn't pick up the file name even in the same file (where it used to work). Sorry, very new to this, so don't really understand. Your help is much appreciated

 

[RoryA]

It must be declared somewhere or the code would never input a file name since it would be a separate variable in each routine.