Dear all,
First off, thank you in advance to anyone who posts an answer to this question - I really appreciate your help!
I am using Excel 2010 to try and make a macro that will try and automate a rather tiresome process of creating many excel workbooks with certain information extracted from a master excel workbook.
I have so far managed to successfully extract information into another section of the same workbook, but would like to copy this information into specific cells in a new template workbook. I am getting stuck on the copying step. Could anyone advise on the best way to copy information to a new workbook and what I'm doing wrong?
Your time would be very much appreciated - thank you so much!
Private Sub CommandButton2_Click()
Dim FName As String
Dim FPath As String
Dim k As Integer
k = 1
For k = 1 To 1000
(cycling through all of the different names to save the workbook as)
FPath = "C:\Users\hdbkiwi\Documents\"
FName = Cells(k, 21).Value
Workbooks.Add ("C:\Users\hdbkiwi\Documents\FormBTemplate.xlsx")
ActiveWorkbook.Sheets("CRC Form B+").Range("G2").Value = Application.Workbooks("Source List for Form B's - Fiddling").Worksheets(1).Cells(1, 1).Value
(This is the problem here - the workbook is not copying anything into the new workbook (from Source List to CRC Form B+) - ideally I'd like to cycle along using k to copy row 1 into new workbook 1, (as well as an index match step that I'd also need to copy in but havent got there yet) and save using the 21st column as the file name of the workbook)
ActiveWorkbook.SaveAs Filename:=FPath & FName
ActiveWorkbook.Close
Next k
End Sub
First off, thank you in advance to anyone who posts an answer to this question - I really appreciate your help!
I am using Excel 2010 to try and make a macro that will try and automate a rather tiresome process of creating many excel workbooks with certain information extracted from a master excel workbook.
I have so far managed to successfully extract information into another section of the same workbook, but would like to copy this information into specific cells in a new template workbook. I am getting stuck on the copying step. Could anyone advise on the best way to copy information to a new workbook and what I'm doing wrong?
Your time would be very much appreciated - thank you so much!
Private Sub CommandButton2_Click()
Dim FName As String
Dim FPath As String
Dim k As Integer
k = 1
For k = 1 To 1000
(cycling through all of the different names to save the workbook as)
FPath = "C:\Users\hdbkiwi\Documents\"
FName = Cells(k, 21).Value
Workbooks.Add ("C:\Users\hdbkiwi\Documents\FormBTemplate.xlsx")
ActiveWorkbook.Sheets("CRC Form B+").Range("G2").Value = Application.Workbooks("Source List for Form B's - Fiddling").Worksheets(1).Cells(1, 1).Value
(This is the problem here - the workbook is not copying anything into the new workbook (from Source List to CRC Form B+) - ideally I'd like to cycle along using k to copy row 1 into new workbook 1, (as well as an index match step that I'd also need to copy in but havent got there yet) and save using the 21st column as the file name of the workbook)
ActiveWorkbook.SaveAs Filename:=FPath & FName
ActiveWorkbook.Close
Next k
End Sub
