PDA

View Full Version : Solved: It sounds simple yet I can not overcome.



exwarrior187
06-30-2009, 01:55 PM
I have what seems like a simple vba script that I've been trying to write to perform what seems like a simple set of tasks.

I have 3 worksheets in a workbook, "template", "vlookups", and "list". My objective is to take a value from the first column of the "list" sheet, put it into a specific cell in the "vlookups" sheet (which populates the rest of this sheet), copy it, create a new worksheet named for the value passed from "list" to "vlookups" and paste the values and formatting and then go back to the "list" sheet and continue the before mentioned steps while moving down the first column . The "template" sheet is really just there and I'm not doing much with it, in fact it looks exactly like "vlookups" but it is blank.

i'm really hoping that the expert who decides to field this will "teach me to fish", instead of "catching me a fish". Sadly I've been trying to use the Macro Recorder to get me started on the scripting. :( Many thanks in advance.

Simon Lloyd
06-30-2009, 11:24 PM
Naturally we can teach you to fish but not without equipment and some effort on your behalf, why not supply a sample workbook and the code that you have already written, from there we can guide you to your end result :)

The macro recorder is a fantastic tool!

exwarrior187
07-01-2009, 07:46 AM
Simon-

I was actually able to get the script to do exactly as I wanted by showing a little more patience with the Macro Recorder and a little research on the syntax of Do...Until Loops. And some ScreenUpdating as well.

Here is what I created:



Sub ListToSheets()
'
' ListToSheets Macro
' Macro recorded 6/25/2009 by Exwarrior187
'
Application.ScreenUpdating = False
Do

SheetName = ActiveCell.Value
Selection.Copy
Sheets("vlookups").Select
Range("C70").Select
ActiveSheet.Paste
Cells.Select
Range("C70").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C70").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Select
ActiveSheet.Name = SheetName
Range("A1").Select
Sheets("list").Select
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Application.ScreenUpdating = True
End Sub

Simon Lloyd
07-01-2009, 08:47 AM
It can probably be shortened to thisSub ListToSheets()
'
' ListToSheets Macro
' Macro recorded 6/25/2009 by Exwarrior187
'
Application.ScreenUpdating = False
Do

SheetName = ActiveCell.Value
Selection.Copy Destination:=Sheets("vlookups").Range("C70")

ActiveSheet.Copy after:=Sheets.Count

ActiveSheet.Name = SheetName
Range("A1").Select
Sheets("list").Select
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Application.ScreenUpdating = True
End Subyou should try not to use select as it slows excel down, i have left the last few selects in as i don't know the significance of them or why you need to select them.