Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-08-27
20
Medium Priority
?
365 Views
Last Modified: 2012-08-27
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
0
Comment
Question by:bvanscoy678
  • 11
  • 9
20 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38337425
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
 

Author Comment

by:bvanscoy678
ID: 38338045
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
 

Author Comment

by:bvanscoy678
ID: 38338171
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 24

Expert Comment

by:Steve
ID: 38338237
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
 

Author Comment

by:bvanscoy678
ID: 38338300
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
 
LVL 24

Expert Comment

by:Steve
ID: 38338371
Likely yes due to Option Explicit

need to
dim NewWb as string
dim x as long

Open in new window

0
 

Author Comment

by:bvanscoy678
ID: 38338435
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
 
LVL 24

Expert Comment

by:Steve
ID: 38338471
try:

Set callback = Workbooks.open Filename:=NewWb

Open in new window

0
 

Author Comment

by:bvanscoy678
ID: 38338547
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
 
LVL 24

Expert Comment

by:Steve
ID: 38338579
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
 

Author Comment

by:bvanscoy678
ID: 38338625
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
 
LVL 24

Expert Comment

by:Steve
ID: 38338633
Use the File SaveAsCopy as this will not change the active workbook. Will just make a copy of the workbook.
0
 

Author Comment

by:bvanscoy678
ID: 38338657
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
 
LVL 24

Expert Comment

by:Steve
ID: 38338665
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
 

Author Comment

by:bvanscoy678
ID: 38338681
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
 

Author Comment

by:bvanscoy678
ID: 38338722
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
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38338792
OK, try this one... couple of small changes... but I think this is it.
roster.xlsm
0
 

Author Closing Comment

by:bvanscoy678
ID: 38338840
Excellent thread and I learned quite a bit about declaring variables and loops. Thanks for all the extra effort!
0
 

Author Comment

by:bvanscoy678
ID: 38338845
Thank you for all the help. I will go over everything in detail and save for the next project!

Enjoy your beer!
Brent
0
 
LVL 24

Expert Comment

by:Steve
ID: 38338856
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question