I have a excel data file and excel template. I need to copy a row of data from data file to a template and save that template as row1 value in data file and go to data file and copy the second row data into template and save the template as as row2 value in data file . I need to keep doing this until the last row of data in data file.
The template rows are fixed and template has two sheets. Only the data file row values keep changing.
I need to first copy A2 value from data file to Template(Sheet1) AB2 cell.
I need to copy a range of C2 TO L2 value from data file to Template(Sheet2)of range Aj11 to AS11.
The cells highlighted in bold of template are not going to change.
With record macro i was able to create a file with one row value how do i change the below macro to loop through each row of data file and create new files.
Any help would be much appreciated. I have uploaded data and template file
Sub Macro1() Dim wbk As Workbook Dim wbk1 As Workbook Dim ws As Worksheet Dim Numb As Integer strFirstFile = "C:\Salma\Surveys\Merchandising\Test-Report\Data.xlsx" strSecondFile = "C:\Salma\Surveys\Merchandising\Test-Report\Template.xlsx" Application.DisplayAlerts = False Application.ScreenUpdating = False Set wbk1 = Workbooks.Open(strFirstFile) Range("A2").Copy Set wbk = Workbooks.Open(strSecondFile) With wbk.Sheets("Sheet1").Select Range("AB2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End With wbk1.Activate Range("C2:L2").Copy wbk.Activate With wbk.Sheets("Sheet2").Select Range("AJ11:AS11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End With wbk.saveas Filename:="C:\Salma\Surveys\Merchandising\Test-Report\" & Range("AB2"), _ CreateBackup:=False Application.DisplayAlerts = True wbk.Close SaveChanges:=False Application.ScreenUpdating = True End Sub