Liagala
04-11-2010, 08:03 PM
I can set up all sorts of interesting formulas in excel itself, but macros are still a foreign language. I can record one, understand what it says, and make minor edits, but that's about it.
I'm making a sheet to track cost information for a school project. There's a data entry page with all sorts of color coding and validation so people can't screw it up too much, a data tables page (hidden), and assorted analysis pages. There will be 15 different groups of students using this, each with their own section on the data entry (scroll down to find your group, don't write in someon else's spot), and each with their own data table. I'm trying to write a macro that copies information from the data entry page and pastes it into the tables, so the analysis can go find it easily. I have the basic macro done, but only for one group. It's just inserting a column followed by a bunch of copy/paste values. I need to duplicate the C&P part of it 14 more times, but I don't want to go and manually change E5 to E52, then to E99, etc. Each group has 10 bits of information to be copied, times 15 groups. That's a lot of manually changing cell references, when I know there has got to be an easier way.
So, how do I write this...
Sheets("Data Entry").Select
Range("E5").Select
Selection.Copy
Sheets("Data Tables").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
... in a different way, to make it work without having to change 300 cell references? If it helps, each group's section of the Data Tables page will be a named range, to make lookups easier in the analysis.
I'm making a sheet to track cost information for a school project. There's a data entry page with all sorts of color coding and validation so people can't screw it up too much, a data tables page (hidden), and assorted analysis pages. There will be 15 different groups of students using this, each with their own section on the data entry (scroll down to find your group, don't write in someon else's spot), and each with their own data table. I'm trying to write a macro that copies information from the data entry page and pastes it into the tables, so the analysis can go find it easily. I have the basic macro done, but only for one group. It's just inserting a column followed by a bunch of copy/paste values. I need to duplicate the C&P part of it 14 more times, but I don't want to go and manually change E5 to E52, then to E99, etc. Each group has 10 bits of information to be copied, times 15 groups. That's a lot of manually changing cell references, when I know there has got to be an easier way.
So, how do I write this...
Sheets("Data Entry").Select
Range("E5").Select
Selection.Copy
Sheets("Data Tables").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
... in a different way, to make it work without having to change 300 cell references? If it helps, each group's section of the Data Tables page will be a named range, to make lookups easier in the analysis.