Copying using a loop

[Simmo-81]

Hi all

Im after a little help on some vba code, im not brilliant with vba and im learning as i go on by viewing other peoples codes and figuring out how they work.

My problem:-

I have a master spreadsheet which will be used to update items from time to time. An example is a new supplier.

I then have 22 spreadsheets in a folder (not the same folder as the master spreadsheet but i could move the sheet there).

I want to be able to copy a range from the master spreadsheet to the 22 other spreadsheets by looping through them.

My range is "A1:A30" on the master spreadsheet and the sheet name is called data. The location on the 22 spreadsheets i want it to go to is "sheet2" range "d1:d30".

There is probably a simple way to do this but i cant seem to find a answer.

Any help on this would be greatly appreciated.

[Bob Phillips]

What is the criteriua for copying to which spreadsheet?

What does the data look like?

Got an example?

[p45cal]

Two solutions offered.
1. You want to be able to choose which files within a folder to update:[vba]Sub blah()
Dim MyFiles As Variant, i As Long
MyFiles = Application.GetOpenFilename(",*.xls", , "select the files you want to process", , True)
If TypeName(MyFiles) <> "Boolean" Then
For i = LBound(MyFiles) To UBound(MyFiles)
Set NextWB = Workbooks.Open(MyFiles(i))
ThisWorkbook.Sheets("Data").Range("A1:A30").Copy Destination:=NextWB.Sheets("Sheet2").Range("D130")
'NextWB.Close Savechanges:=True
Next i
End If
Set NextWB = Nothing
End Sub[/vba]Use the standard Ctrl and/or Shift keys to multiselect the files.
2. You know for sure that it'll be the same files over and over:[vba]Sub blah2()
Dim MyFiles As Variant, i As Long
MyFiles = Array( _
"C:\Documents and Settings\Simmo81\My Documents\testfolder\Book1.xls", _
"C:\Documents and Settings\Simmo81\My Documents\testfolder\Booksdfgsdg4.xls", _
"C:\Documents and Settings\Simmo81\My Documents\testfolder\Booksdfgsdg3.xls", _
"C:\Documents and Settings\Simmo81\My Documents\testfolder\Booksdfgsdg2.xls")

For i = LBound(MyFiles) To UBound(MyFiles)
Set NextWB = Workbooks.Open(MyFiles(i))
ThisWorkbook.Sheets("Data").Range("A1:A30").Copy Destination:=NextWB.Sheets("Sheet2").Range("D130")
'NextWB.Close Savechanges:=True
Next i
Set NextWB = Nothing
End Sub
[/vba]In both cases there's a line you can uncomment to close the files and save changes, currently it just leaves the files open so that you can see that the job has been done.
In the second case, if you have 22 files, you may have to reduce the number of continuation characters (" _") in the line beginning "MyFiles =" as there is an upper limit to the number of them appearing in one statement.

[Simmo-81]

thanks for the input all.

p45cal i used your 2nd code and adjusted it to my needs, it works perfect.

However something i have noticed and something that will probably happen.

If a workbook is already open it cuts the macro short, i usually use a workbook open code to check if workbook is open but not sure how i would do this for this code.

Would it be possible to start the macro, skip any that are open and have them saved so i can alert them with a msgbox that data wasnt copied. i think its a do while loop but not sure

I might be asking too much here sorry

[Bob Phillips]

[vba]

Sub blah2()
Const FilePath As String = "C:\Documents and Settings\Simmo81\My Documents\testfolder\"
Dim MyFiles As Variant, i As Long
Dim NextWB As Workbook
MyFiles = Array("Book1.xls", "Booksdfgsdg4.xls", "Booksdfgsdg3.xls", "Booksdfgsdg2.xls")

For i = LBound(MyFiles) To UBound(MyFiles)
Set NextWB = Nothing
On Error Resume Next
Set NextWB = Workbooks(MyFiles(i))
On Error GoTo 0
If NextWB Is Nothing Then Set NextWB = Workbooks.Open(FilePath & MyFiles(i))
ThisWorkbook.Sheets("Data").Range("A1:A30").Copy Destination:=NextWB.Sheets("Sheet2").Range("D130")
'NextWB.Close Savechanges:=True
Next i
Set NextWB = Nothing
End Sub
[/vba]