HELLO,
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