Modifying a code used to auto populate in a loop.

[tony0217]

I currently have a code that I have used before to auto populate a cell in a worksheet.

I have a list of cells that I need to populate.

there are two lists on sheet 2.
the first list is in column C.
the second list is in column J.

the cells that need to be populated from column C sheet 2, to the cells in sheet 1 are:
C6
B81
F84
F85
B88
D97.

the cells that need to populated from column J sheet 2, to the cells in sheet 1 are:
f66.

the code below does work for colum C.

what i need to do is modify how to use more than one list to populate the cells.

Also would you please show me how to break this down just in case i need to add another list?

Thank you.


The below code populates cell c13 with a value from the list in sheet 2, saves the file as the value of c13 and then moves on to the next value in the list and over and over again.

Sub MM1()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
For Each c In sh2.Range("A1", sh2.Cells(Rows.Count, 1).End(xlUp))
    If c <> "" Then
        sh1.Range("c13") = c.Value
        fname = c.Value
        ActiveWorkbook.SaveCopyAs "C:\data\" & fname & ".xls" 'change path to suit
    End If
Next c
 sh2.Range("A1", sh2.Cells(Rows.Count, 1).End(xlUp)).ClearContents
End Sub

 

[tony0217]

The list is on page 2 of the same workbook.

 

[tony0217]

also how do I change the saveas to a .txt file?

 

[tony0217]

Anyone?

 

[tony0217]

Please help.