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

Caylus

New Member
Joined
Mar 22, 2011
Messages
2
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!:confused:

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 :confused:

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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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:

Code:
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

New Member
Joined
Mar 22, 2011
Messages
2
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
To close the current workbook:

Activeworkbook.Close

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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,149
Messages
5,600,012
Members
414,355
Latest member
Aruna Kallu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top