Create loop to copy row data into new workbook (template)

Good morning,

As sad as it is, this is the furthest I could get with my question (attached file and code).

I attempted to set my variables, but got lost and ended up using cells references which wasn't what I wanted. What I wanted to do was assign variables and then use a loop to go through a list of names and copy them to a template, which I would then save, clear and use again for the next row of new incoming data (I guess I would not need to clear it).

I have two workbooks which will be open and saved in the same folder. The first workbook called ROSTER will contain 1 worksheet named "list". The "list" worksheet will contain about 250 rows of the exact same data type, but different values. My goal is to copy the data from the row in the Roster Workbook. List Worksheet and paste special values into an Excel Template workbook named "CallBack", with a worksheet named "template".  After all the data from the row was copied over, I would then save the workbook into the same folder.  Not sure if I would need to clear the template or just paste over the old data.

I have spent a lot of time reading about assigning variables and loops, but I just don't have the knowledge base yet to pull it off. Any help is greatly appreciated and I will diligently study any code to place into my project.

I was not able to upload the callback files as a template and maybe it doesn't need to be a template. I thought it would be easier to save it and not have to open a new file each time in the loop.

Thanks,
Brent






Option Explicit

Dim roster As Workbook
Dim callback As Workbook
'The call back workbook is a Excel Temmplate WorkBook
Set roster = wb
Set callback = NewWb

' copy cells from source workbook into template (attempt loop)
ThisWorkbook.Sheets("list").Cells(2, 1).Copy
wb.Sheets("template").Cells(3, 2).PasteSpecial Paste:=xlValues
ThisWorkbook.Sheets("list").Cells(2, 2).Copy
wb.Sheets("template").Cells(3, 4).PasteSpecial Paste:=xlValues
ThisWorkbook.Sheets("list").Cells(2, 3).Copy
wb.Sheets("template").Cells(3, 1).PasteSpecial Paste:=xlValues
ThisWorkbook.Sheets("list").Cells(2, 4).Copy
wb.Sheets("template").Cells(4, 1).PasteSpecial Paste:=xlValues
ThisWorkbook.Sheets("list").Cells(2, 5).Copy
wb.Sheets("template").Cells(4, 2).PasteSpecial Paste:=xlValues
ThisWorkbook.Sheets("list").Cells(2, 6).Copy
wb.Sheets("template").Cells(4, 4).PasteSpecial Paste:=xlValues
ThisWorkbook.Sheets("list").Cells(2, 7).Copy
wb.Sheets("template").Cells(4, 11).PasteSpecial Paste:=xlValues
ThisWorkbook.Sheets("list").Cells(2, 8).Copy
wb.Sheets("template").Cells(4, 12).PasteSpecial Paste:=xlValues

'Save new template as macro-enabled workbook format ( .xlsm )
'save as the file: name of the contents of NewWB .cells(3,4) ' the last name, first name

'clean the contents of the previously filled out template
'wb.Sheets("template").Cells(3, 2)
'wb.Sheets("template").Cells(3, 4)
'wb.Sheets("template").Cells(3, 1)
'wb.Sheets("template").Cells(4, 1)
'wb.Sheets("template").Cells(4, 4)
'wb.Sheets("template").Cells(4, 11)
'wb.Sheets("template").Cells(4, 12)

' have the loop continue through the Roster Workbook.Sheet("list")

Open in new window

roster.xlsm
CALLBACK.xlsm
bvanscoy678Asked:
Who is Participating?
 
SteveCommented:
OK, try this one... couple of small changes... but I think this is it.
roster.xlsm
0
 
SteveCommented:
OK, can we take this back to the start as the code there is in need of some work...

The workbooks should be declared:

Dim roster As Workbook
Dim callback As Workbook
Dim wsList as WorkSheet
Set roster = ThisWorkbook
set wsList = roster.sheets("List")

NewWB = "C:\some_folders\filename.xlsm"
Set callback = Workbooks.open NewWb

Open in new window


the Copy pastepecial is not needed you can just do one ref = another ref:

for x = 2 to 10
callback.Sheets("template").Cells(x, 2) = wsList.Cells(x, 1)
callback.Sheets("template").Cells(x, 3) = wsList.Cells(x, 2)
'etc etc
next x

Open in new window


I am sure if we understand your loop we can set that too.
0
 
bvanscoy678Author Commented:
Okay, sorry for the delay, I had to leave the office for a bit.

I'll update what you commented and I'll check right back.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bvanscoy678Author Commented:
Okay, I made the changes, but not sure I did it correctly. What I can see is that you are making the "templates" worksheet = to the "list" worksheet.

For x = 2 To 10
callback.Sheets("template").Cells(3, 2) = wsList.Cells(x, 1)

The X will be the rows of the list worksheet and the corresponding or left side identifies what Cells the results will show. I was not sure if I need to change the X on the left side of the equation, but since it the cell locations are not always on the same row, this is what I came up with.

Option Explicit

Dim roster As Workbook
Dim callback As Workbook
Dim wsList As Worksheet
Set roster = ThisWorkbook
Set wsList = roster.Sheets("List")

NewWb = "C:\Users\Brent\Desktop\callback.xlsm"
Set callback = Workbooks.open NewWb

' copy cells from source workbook into template (attempt loop)
For x = 2 To 10
callback.Sheets("template").Cells(3, 2) = wsList.Cells(x, 1)
callback.Sheets("template").Cells(3, 4) = wsList.Cells(x, 2)
callback.Sheets("template").Cells(3, 1) = wsList.Cells(x, 3)
callback.Sheets("template").Cells(4, 1) = wsList.Cells(x, 4)
callback.Sheets("template").Cells(4, 2) = wsList.Cells(x, 5)
callback.Sheets("template").Cells(4, 4) = wsList.Cells(x, 6)
callback.Sheets("template").Cells(4, 11) = wsList.Cells(x, 7)
callback.Sheets("template").Cells(4, 12) = wsList.Cells(x, 8)
Next x


'Save new template as macro-enabled workbook format ( .xlsm )
'save as the file: name of the contents of NewWB .cells(3,4) ' the last name, first name

'clean the contents of the previously filled out template
'wb.Sheets("template").Cells(3, 2)
'wb.Sheets("template").Cells(3, 4)
'wb.Sheets("template").Cells(3, 1)
'wb.Sheets("template").Cells(4, 1)
'wb.Sheets("template").Cells(4, 4)
'wb.Sheets("template").Cells(4, 11)
'wb.Sheets("template").Cells(4, 12)

' have the loop continue through the Roster Workbook.Sheet("list")

Sub CreateCallbackList()

End Sub

Open in new window


Thanks,
Brent
0
 
SteveCommented:
erm,  you don't seem to have Sub CreateCallbackList() statement in the right place

It should be under Option Explicit not at the end

once you have moved the Sub CreateCallbackList to the start under Option Explicit you should be able to press [F8] to run the macro one line at a time
0
 
bvanscoy678Author Commented:
Yes, I missed that. Thanks.

I get an error on this line of code.
NewWb = "C:\Users\Brent\Desktop\callback.xlsm"
I think it is because I did not declare NewWb ?

Thanks
0
 
SteveCommented:
Likely yes due to Option Explicit

need to
dim NewWb as string
dim x as long

Open in new window

0
 
bvanscoy678Author Commented:
It fixed that, but now error on the:

Set callback = Workbooks.open NewWb

Option Explicit
Sub CreateCallbackList()

Dim NewWb As String
Dim x As Long
Dim roster As Workbook
Dim callback As Workbook
Dim wsList As Worksheet
Set roster = ThisWorkbook
Set wsList = roster.Sheets("List")
NewWb = "C:\Users\Brent\Desktop\callback.xlsm"
Set callback = Workbooks.open NewWb

Open in new window

0
 
SteveCommented:
try:

Set callback = Workbooks.open Filename:=NewWb

Open in new window

0
 
bvanscoy678Author Commented:
nope, still got an error. Sorry for the hassles, this is what I was up againts with declaring my variables. I know it is the right way, but makes it tough to gain traction! thanks

I tried this also:

Sub CreateCallbackList()

Dim NewWb As String
Dim x As Long
Dim roster As Workbook
Dim callback As Workbook
Dim wsList As Worksheet
Set roster = ThisWorkbook
Set wsList = roster.Sheets("List")
Workbooks.Open Filename, "C:\Users\Brent\callback.xlsm"
Set callback = NewWb
0
 
SteveCommented:
To be sure please check the file name (with extansion and filepath) is correct
then try:

Set callback = Workbooks.Open(Filename:=NewWb)

Open in new window

I am here with a beer for a while, so we will get there :)
0
 
bvanscoy678Author Commented:
Okay, that did the trick. The loop works!

I stepped through it several times and watched it populate the cells. As it works right now, it overwrites each other.

I need to add a file save as before the

Next x

Let me see if I can figure it out. BRB
0
 
SteveCommented:
Use the File SaveAsCopy as this will not change the active workbook. Will just make a copy of the workbook.
0
 
bvanscoy678Author Commented:
I was able to add the file save as, but not sure if it is the best way to do it. They all saved correctly into the folder except row 6. I'll take a look and see if I can figure out way.

For x = 2 To 10
callback.Sheets("template").Cells(3, 2) = wsList.Cells(x, 1)
callback.Sheets("template").Cells(3, 4) = wsList.Cells(x, 2)
callback.Sheets("template").Cells(3, 1) = wsList.Cells(x, 3)
callback.Sheets("template").Cells(4, 1) = wsList.Cells(x, 4)
callback.Sheets("template").Cells(4, 2) = wsList.Cells(x, 5)
callback.Sheets("template").Cells(4, 4) = wsList.Cells(x, 6)
callback.Sheets("template").Cells(4, 11) = wsList.Cells(x, 7)
callback.Sheets("template").Cells(4, 12) = wsList.Cells(x, 8)
FPath = "C:\Users\Brent\Desktop\callback"
FName = Sheets("template").Range("d3").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

Next x

Open in new window

0
 
SteveCommented:
I would still use the SaveAsCopy
If you would like to post a copy of the workbook as it stands I can take a look.

just to confuse :)
I would replace the from x  = 2 to 10 with a better version:

x = 2
Do while len(cells(x,1))<>0

'looped code here
x = x+1
loop

Open in new window

0
 
bvanscoy678Author Commented:
I see the difference (according to the Sesame Street saying of "Which one of these don't  belong").

The one that is wrong has a middle initial which probably messed up the file save as

I will add the code change and post the workbook. thanks
Capture.PNG
0
 
bvanscoy678Author Commented:
Well, I goobered it up somehow!

I have an error with the  the variable name for thisfile =

Plus, not sure where to put the path for the file save as target folder.

I attached the 2 workbooks and the code.

Thanks
CALLBACK.xlsm
roster.xlsm
0
 
bvanscoy678Author Commented:
Excellent thread and I learned quite a bit about declaring variables and loops. Thanks for all the extra effort!
0
 
bvanscoy678Author Commented:
Thank you for all the help. I will go over everything in detail and save for the next project!

Enjoy your beer!
Brent
0
 
SteveCommented:
You are welcome, I started with this kind of thing so I know a little help can go a long way.
This site is good for that. Soon enough I am sure you will be answering too :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.