[Excel 2011]:Writing from one Excel file to enother- Dynamic way

[Hashmelator]

Hi all,
I have one file that contains three rows of information (Plz see the attached file) and I want to copy each row to a different file. Each row should be copy to a certain file that his name is located in the first file in row D.
In the end I expect to find the original file with addition of 3 files with data: File1.xlsx, File2.xlsx, File3.xlsx.

Is there an option in VB to do it like that?
Thanks in ahead!

[opus]

Welcome to VBForums

Yes, this can be done using VBA.
You can try to use the macro-recoder.
Using the macro-recorder, perform all steps needed, stop the recorder and look at the code!

[westconn1]

if the names in column D are existing files then open the files, else add a workbook, then saveas
example for the latter

rw = 1
do until isempty(range("a1"))
  set newbk = workbooks.add
  newbk.sheets(1).range("a1:e1") = range(cells(rw, 1),cells(rw, 5))
  newbk.saveas thisworkbook.path &"\" & cells(rw, 4) & ".xlsx"  ' other parameters if required
  newbk.close
  rw = rw + 1
loop

[opus]

I'd change to start of the loop to.

do until isempty(cells(rw,1))

otherwise to loop will run till...........................

[westconn1]

I'd change to start of the loop to.

good idea

[Hashmelator]

Thank you guys for your very quick response.
Opus, the kind of solution that you suggested will work only in the private case like I attached. I need a general solution that will even work tomorrow in the same way if I would have a table that the orders of the rows will not be the same.
westconn1, great loop, but is it run in your computer?
Are you sure that ThisWorkbook.Path & "\" & Cells(rw, 4) & ".xlsx" is the right way to write it?
I found that &Cells(rw, 4) doesn't put the data in the address.

Thanks again.

[westconn1]

Are you sure that ThisWorkbook.Path & "\" & Cells(rw, 4) & ".xlsx" is the right way to write it?

yes, unless this is apple version or some such, then god knows

I found that &Cells(rw, 4) doesn't put the data in the address.

what address?
to test the results

msgbox cells(rw, 4)
'or
msgbox thisworkbook.path
msgbox thisworkbook.path & "\" & cells(rw, 4)

[Hashmelator]

Do you know what could be the reason that it doesn't work?
For some reason I get a message "Run time error '1004': application defined or object defined error"...

[nO_OnE]

If I am not mistaken it is because the way you setup your calculation for your 'rw' variable. rw will be 1 more than the last row you put data in.

If I am right, try:

MsgBox ThisWorkbook.Path & "\" & Cells(rw, 4).Offset(-1, 0)
 
Or
 
    rw2 = rw - 1
    MsgBox ThisWorkbook.Path & "\" & Cells(rw2, 4)

[westconn1]

where you tried that in button4 rw had no value assigned (therefore 0), row(0) does not exist and will give 1004 error, you must assign value to rw

if you have declared rw in the general section an assign value elsewhere, please disregard the above