copying a range from one file to multiple files in a folder

[Suryaprakash]

Sometime back I have posted a new thread but did not get any replies, may be because my question is not clear. I will try to explain my problem in details. I have one folder "c:\Folder1" in which there are 250 files with a similar structure. I have one more file in "C:\Folder3" named Vikas.xls. I want to copy a range "L1:N500" from this file to all the 250 files in Folder1 at L1 position. Could anyone help me out with this problem. I am using Excel 2003. I think my problem is now and there is no confusion.

Thanks.

 

[John_w]

Put this code in a module in Vikas.xls and run it on a copy of C:\Folder1.

Sub Copy_Range_To_All_Workbooks_In_Folder()
    
    Dim folder As String, fileName As String
    Dim masterRange As Range
    
    folder = "C:\Copy of Folder1\"
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    Set masterRange = Sheets("Sheet1").Range("L1:N500")
    
    fileName = Dir(folder & "*.xls")
    Do While fileName <> ""
        Workbooks.Open folder & fileName
        masterRange.Copy ActiveWorkbook.Sheets(1).Range("L1")
        ActiveWorkbook.Close savechanges:=True
        fileName = Dir
    Loop
    
End Sub

 

[Suryaprakash]

Dear John:

Thanks for the code. Unfortunately, it is giving Run time error 9 (Subscript out of range) Debug points to : Set masterRange = Sheets("Sheet1").Range("L1:N500")

I made a new folder C:\Copy of Folder1. Then I copied the formula in Vikas.xls (which is in C:\Folder3) by inserting a new module. I then opened Vikas.xls and ran the code which gave the above message.

Please look into the problem and suggest any modifications or whether my procedure was wrong.

Thanks.

 

[John Davis]

Dear John:

Thanks for the code. Unfortunately, it is giving Run time error 9 (Subscript out of range) Debug points to : Set masterRange = Sheets("Sheet1").Range("L1:N500")

I made a new folder C:\Copy of Folder1. Then I copied the formula in Vikas.xls (which is in C:\Folder3) by inserting a new module. I then opened Vikas.xls and ran the code which gave the above message.

Please look into the problem and suggest any modifications or whether my procedure was wrong.

Thanks.

Try changing this:

Set masterRange = Sheets("Sheet1").Range("L1:N500")

to this:

Set masterRange = Workbooks("Vikas.xls").Sheets("Sheet1").Range("L1:N500")

 

[John_w]

Unfortunately, it is giving Run time error 9 (Subscript out of range) Debug points to : Set masterRange = Sheets("Sheet1").Range("L1:N500")

What is the name of the sheet containing the L1:N500 range? If it isn't "Sheet1", change that line to the appropriate sheet name.

Your procedure for running the code is correct.

 

[Suryaprakash]

I have carried out the change as suggested by you "

Set masterRange = Workbooks("Vikas.xls").Sheets("Sheet1").Range("L1:N500")

Now, when I run the code, there is no error message, but there is no action on the files in "C:\Copy of Folder1" either. The name of the sheet containing the range was "Vikas" which I renamed to "Sheet1" and then tried running the code, but still no desired result. I am using Excel 2000 and all my files have 1 sheet only. The names of the sheets are always the same as the names of the FILES. I have tried with only 5 files to start with. By the way i want the range to be copied at L1:N500 position only and not at the bottom. I hope with this information you would be able to figure out the problem and modify the code accordingly.

Thanks and sorry for the trouble.

 

[wigi]

I did not test it explicitly, but below code should be working.

Basically, I corrected a typo from John w.

If not, insert a MsgBox or Debug.Print to show the contents of variables. Also, use F8 to step through the macro.

Sub Copy_Range_To_All_Workbooks_In_Folder()
    
    Dim folder As String, fileName As String
    Dim masterRange As Range
    
    folder = "C:\Copy of Folder1\"
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    Set masterRange = ThisWorkbook.Sheets("Sheet1").Range("L1:N500")
    
    fileName = Dir(folder & "*.xls")
    Do While fileName <> ""
        Workbooks.Open folder & fileName
        masterRange.Copy ActiveWorkbook.Sheets(1).Range("L1")
        ActiveWorkbook.Close savechanges:=True
        fileName = Dir()
    Loop
    
End Sub

 

[Suryaprakash]

Hi Wigi

Thanks for your help. Even your modified code did not work. The files in the folder are unchanged. As far as your other suggestion is concerned "If not, insert a MsgBox or Debug.Print to show the contents of variables. Also, use F8 to step through the macro.", it is beyond my comprehension as I do not have any knowledge of macros.

I suggest you try on a few dummy files (just 3 files in a folder, and the main data file, (all filled up with autofilled data), so that you can figure out the problem.

Thanks again for your assistance and guidance.

 

[wigi]

Now I tested the, and without changing anything, it works on the 3 files you suggested me to make.

Do the same and you will see that it works.

A line of code like:

MsgBox folder & fileName

can be used to verify the contents of the variables folder and fileName. Please search for documentation on MsgBox and Debug.Print, because this is very basic. You will find it in a lot of other topics here on the board.

 

[Suryaprakash]

Hi Wigi:

Thanks for your reply and testing the code. I ran the code now on 3 files in the folder and it is working ok! The only explaination seems to be that I had made a silly mistake of putting a space in Folder 1 instead of Folder1. Later on, I checked the code on actual files and it is working exceedingly well. Thereafter I also checked the original code by John W and even that is working ok.

Thanks again for your kind assistance.