Macro that will take a row with formulas and paste them in each file

[copleyr]

Hello all,

I have a row with formulas in each cell. I need a macro that can copy this row and paste it in a tab labeled "DEFAULTS" on row 70, in each excel file, in a desktop folder labeled "ALL".

I need the formulas to be authentic to each file (non-linked to other files).

Is this possible? I need this to be a macro because there are >200 of these excel files. Each file has the same tab labeled "DEFAULTS"

Thank you very much in advance.

 

[copleyr]

It didn't like that one at all. It just kept looping on the same file and asking the same update links question

 

[wigi]

I'm off to bed now, almost 1AM... See you later if no other helper comes up with a solution.

 

[copleyr]

Thanks again Wigi

 

[wigi]

So, without the parameter 2 it runs and asks once for every file in the loop, the question to update links or not.

With the parameter 2, you are asked the same question a number of times, for every file in the loop?

I do not follow this one I'm afraid. It should not ask anything at all with that parameter.

 

[copleyr]

With parameter 2, it asks the same question, for the first file, over and over again.

I'm unsure why. It's still a great macro though; I just have to sit here and keep pressing the "dont update" links button, 250 times

 

[copleyr]

Here is a similar program. Had the same situation where the "Application.DisplayAlerts = True" worked. Not sure if this could be of any help...

Sub Copy_dem_files()
Dim MyPathName As String
Dim MyFileName As String
Dim NumChars As Long
Dim X As Long
Dim SummarySheet As String
Workbooks.Add
SummarySheet = ActiveWorkbook.Name
MyPathName = "C:\Desktop\All/"
MyFileName = Dir(MyPathName)
X = 0
Do While MyFileName <> ""
    X = X + 1
    Workbooks.Open MyPathName & MyFileName, False
    Application.DisplayAlerts = False
    Sheets("Defaults").Range("A70:CO70").Copy
    Workbooks(SummarySheet).Activate
    Range("A" & X & ":CO" & X).PasteSpecial xlPasteValuesAndNumberFormats
    Range("A" & X & ":CO" & X).PasteSpecial xlPasteFormats
    Workbooks(MyFileName).Close False
    MyFileName = Dir
Application.DisplayAlerts = True
Loop
End Sub

 

[wigi]

Can you email me a couple of input files? And also the file containing the code ("ThisWorkbook" in the VBA code)

 

[wigi]

Hi there

I can reproduce the problem, I am asked 3 times per copy/paste operation (or transferring the formulas) or the origin of the sheets. That is:

- sheet Financial-Corp
- sheet Fin Review Team
- sheet Financial-GS

If I just add these sheets as empty sheets to the file with the code, and I "F2-Enter" all formulas in row 2, it works without asking.
Because now Excel can trace the origin of the sheets.

I do not know whether these 3 sheets in your file? Do you get the same question for updating for these 3 sheets?

Regards,

Wim