I am recording a macro for every line I need data for. There has to be a better way.... Argggggg!

[Caylus]

I am so new to macros. I have always just recorded my macro and then looked at the code with alt-f11 to make changes. But that fails me here because I cannot record what I want it to do.

I have two problems. So, I have a list in A, like this-- but with 300+ lines:

Fred
Sam
Helen
George

And I have a csv file for each of these people in a single folder.

When I recorded my macro I got this:

Workbooks.Open Filename:="C:\resultsof3rdgradetests\Fred.csv"

So, I was trying brute force, and recording my macro for every name. I got to the 12th name when I gave up... it will take me days to make a macro for every person, uggg!

But here is what the rest of my macro does...

(1) I open the .csv file for the name,
(2) I copy the data into a worksheet on my main spreadsheet (and close the .csv file)
(3) I do some stuff to the worksheet to get answers,
(4) I transfer the answers to each name's line (a2,a3,a4,a5 for Fred, etc.)

What I cannot figure out, but there must be a way, Is I want the macro to be smart and do this (stuff that is over my head in bold):
(1) Look at A1
(2) Open .csv file WITH THAT NAME
(3) copy the data from the .csv to a worksheet on the main spreadsheet (and close the .csv file WITH THAT NAME!)
(4) Do some stuff to the worksheet to get answers,
(5) Transfer the data TO THE EXACT LINE THAT I HAPPEN TO BE ON, THE LINE THAT CORRESPONDS TO THE NAME THAT WE JUST OPENED,
(6) Drop down to the next line and do all these steps again until the end of the list.


So here is the code I came up with recording. So is there a way to make a loop? A way to have each line trigger the opening of the file? A way to drop to the next line? I am so burned out from trying to do this by recording a macro for each name and I am running out of time

Here is how I was doing it:

Sub testresultsforAMFKmeeting()
'
' StudentResults Macro
' Macro recorded 3/21/2011 by caylus
'

'
Sheets("MAIN").Select
[A3 is Jackson. I want to write the name on H3, so I know the data matches in case I make a mistake with the macro. But what I really want is for this to just go through line by line and look at the name and open the right .csv file, figure out the data, write it on the line and go to the next one.]
Range("B3").Select
Range("H3") = "Jackson"
Workbooks.Open Filename:="C:\resultsof3rdgradetests\Jackson.csv"
Range("A1:G1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("SLV_forMy_Macros.xls").Activate
Sheets("worksheet1").Select
Range("A1").Select
ActiveSheet.Paste
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("FORMAT").Select
Rows("1:1").Select
Selection.Copy
Sheets("worksheet1").Select
ActiveSheet.Paste
Sheets("FORMAT").Select
Range("B26").Select
Selection.End(xlDown).Select
Range("B708").Select
Sheets("worksheet1").Select
ActiveWindow.LargeScroll Down:=19
Range("A708:F708").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A707:F707").Select
Range(Selection, Selection.End(xlUp)).Select
Range("A2:F707").Select
Range("A707").Activate
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.LargeScroll Down:=-20
Sheets("FORMAT").Select
Range("H687").Select
Selection.End(xlUp).Select
Range("G36:K36").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("worksheet1").Select
Range("G36").Select
ActiveSheet.Paste
Range("L6").Select
Columns("L:L").EntireColumn.AutoFit
Sheets("FORMAT").Select
ActiveWindow.LargeScroll Down:=-19
Range("L1:M7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("worksheet1").Select
Range("L1").Select
ActiveSheet.Paste
Columns("L:L").EntireColumn.AutoFit
Range("L1:M7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MAIN").Select
Range("J1").Select
ActiveSheet.Paste
Columns("J:J").EntireColumn.AutoFit
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=worksheet1!RC[2]"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=worksheet1!RC[2]"
Range("K3:K7").Select
Selection.FillDown
[ok: Here is where I write the data on Jackson's line. I have it here as B3, but what I want is for it to loop and just write the data on the same line that it opened the file for-- or the next blank line which should be the same thing. This writes the 6 pieces of data that I need and then copies and pastes it back as values so when I erase it to be ready for the next one the data is still there.]
Range("B3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[9]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=RC[8]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=R[1]C[7]"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=R[2]C[6]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=R[3]C[5]"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[4]"
Range("B3:G3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K2:K7").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("worksheet1").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A2").Select
Windows("Jackson.csv").Activate
ActiveWindow.Close
Sheets("MAIN").Select
Range("B4").Select

 

[ChrisM]

Yes, there is a much better way to do this. Unfortunately I have to get back to work so can't post very much. You need to step away from the macro recorder for this and learn to write code by hand. Here's the basic loop structure you could use:

Dim mySrcRange as Range
dim rCell as Range
dim temp as string

Set mySrcRange = Sheets("Sheet1").Range("A1:A100") 'this is your list of names

'this is your name loop
For Each rCell in mySrcRange

'open workbook
temp = "C:\resultsof3rdgradetests\" & rCell & ".csv"
Workbooks.Open Filename:=temp 

'now copy your data over
'close workbook
'do stuff
'now all done so increment to next name

Next rCell

Good luck!

 

[Caylus]

Thanks for the help. So, so far, so good. But in terms of closing the open data worksheet I am stuck. I have tried the following:

'close workbook
Windows(Filename = temp).Activate
ActiveWindow.Close

And I've tried:

'close workbook
Windows(temp).Activate
ActiveWindow.Close

And I've tried:

'close workbook
Workbooks.Close Filename:=temp

Which seemed the most logical. But none of these work. How do I specify to close the curent workbook?

Also, I now understand the "dim" thing you did. (You don't see that when you use the recorder!) So I made a "Dim" for a "counter" so I can say what line to write the data to. But what is the syntax for that?

For example, in the way I did it using the recorder I have for line 9:

Range("B9").Select
ActiveCell = Range("K2")

So is it something like:

Range("B"Counter).Select
ActiveCell = Range("K2")

How do you say that exactly without getting an error message?

Thanks for the help!

 

[ChrisM]

To close the current workbook:

Activeworkbook.Close

To use a counter as you loop through all your cells:

dim counter as long

counter = 1
For Each rCell in mySrcRange
   'do something
   sheets("mysheet").cells(counter, 2) = sheets("mysheet").Range("K2")
   'or: sheets("mysheet").Range("B:"&counter) = sheets("mysheet").Range("K2")
   
   'now do more stuff
   counter = counter + 1

Next rCell

I would highly recommend you start going through the VBA help file within Excel, and start reviewing VB/VBA tutorials on this site and the web. Get away from the recorder mentality, it will help point you in the right direction, but it won't show you how to write proper macros.