Creating a variable and pickout data

N

netxel

Hi

Could anybody help me and tell me how to write a macro where the Colum is
chosen by a name.

The rows are always the same, but I would like to make a function witch
allows me to use the same macro regardless if I type January or August. I
have to transfer a lot of simple data from four data sheet to four different
(language) results sheets.

Every month I have to make the same routine

I have pasted a part off the macro that I have made. Simple but need some
help.


My simple but weary large macro:


Sub JanuarNORDIC()
'
' JanuarNORDIC Makro
' Makro indspillet 16-02-2010 af tue.madsen
'
Application.Run "'Safety status Nordic - new template
2009.xls'!Accidentwithlosttime"
Application.Run "'Safety status Nordic - new template
2009.xls'!Accidentwithmodifieddutyandinjuries"
Application.Run "'Safety status Nordic - new template
2009.xls'!Accidentwithoutlosttimebutpersonalinjuries"
Application.Run "'Safety status Nordic - new template
2009.xls'!Accidentwithmaterialdamageds"
Application.Run "'Safety status Nordic - new template
2009.xls'!Accidentsaccumulatedfortheyear"
Application.Run "'Safety status Nordic - new template
2009.xls'!Nearmissanddangerussituations"
Application.Run "'Safety status Nordic - new template
2009.xls'!NearmissAccumulatedForTheYear"
Application.Run "'Safety status Nordic - new template
2009.xls'!AccidentFrequency"

Sheets("DK - Safetystatus").Select
Range("A1").Select

End Sub

-----------------------------------------------------------------------------------

Sub Accidentwithlosttime()
'
' Accidentwithlosttime Makro
' Makro indspillet 16-02-2010 af tue.madsen
'

' DANMARK

Sheets("DK - Safetystatus").Select
' Ulykker med fravær - LTA
Range("C8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R6C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R6C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R6C4"
Range("F8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R6C4"
Range("C9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R13C4"
Range("D9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R13C4"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R13C4"
Range("F9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R13C4"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R20C4"
Range("D10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R20C4"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R20C4"
Range("F10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R20C4"
Range("C11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R27C4"
Range("D11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R27C4"
Range("E11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R27C4"
Range("F11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R27C4"
Range("C12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R34C4"
Range("D12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R34C4"
Range("E12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R34C4"
Range("F12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R34C4"
Range("C13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R41C4"
Range("D13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R41C4"
Range("E13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R41C4"
Range("F13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R41C4"
Range("C14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R48C4"
Range("D14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R48C4"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R48C4"
Range("F14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R48C4"
Range("C15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R55C4"
Range("D15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R55C4"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R55C4"
Range("F15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R55C4"
Range("C16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R62C4"
Range("D16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R62C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R62C4"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R62C4"
Range("C17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R69C4"
Range("D17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R69C4"
Range("E17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R69C4"
Range("F17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R69C4"
Range("A1").Select

' SVERIGE

Sheets("SE - Safetystatus").Select
' Ulykker med fravær - LTA
Range("C8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R6C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R6C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R6C4"
Range("F8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R6C4"
Range("C9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R13C4"
Range("D9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R13C4"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R13C4"
Range("F9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R13C4"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R20C4"
Range("D10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R20C4"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R20C4"
Range("F10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R20C4"
Range("C11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R27C4"
Range("D11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R27C4"
Range("E11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R27C4"
Range("F11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R27C4"
Range("C12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R34C4"
Range("D12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R34C4"
Range("E12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R34C4"
Range("F12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R34C4"
Range("C13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R41C4"
Range("D13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R41C4"
Range("E13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R41C4"
Range("F13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R41C4"
Range("C14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R48C4"
Range("D14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R48C4"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R48C4"
Range("F14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R48C4"
Range("C15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R55C4"
Range("D15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R55C4"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R55C4"
Range("F15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R55C4"
Range("C16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R62C4"
Range("D16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R62C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R62C4"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R62C4"
Range("C17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R69C4"
Range("D17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R69C4"
Range("E17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R69C4"
Range("F17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R69C4"
Range("A1").Select

' NORGE

Sheets("NO - Safetystatus").Select
' Ulykker med fravær - LTA
Range("C8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R6C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R6C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R6C4"
Range("F8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R6C4"
Range("C9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R13C4"
Range("D9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R13C4"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R13C4"
Range("F9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R13C4"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R20C4"
Range("D10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R20C4"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R20C4"
Range("F10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R20C4"
Range("C11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R27C4"
Range("D11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R27C4"
Range("E11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R27C4"
Range("F11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R27C4"
Range("C12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R34C4"
Range("D12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R34C4"
Range("E12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R34C4"
Range("F12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R34C4"
Range("C13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R41C4"
Range("D13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R41C4"
Range("E13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R41C4"
Range("F13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R41C4"
Range("C14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R48C4"
Range("D14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R48C4"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R48C4"
Range("F14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R48C4"
Range("C15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R55C4"
Range("D15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R55C4"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R55C4"
Range("F15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R55C4"
Range("C16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R62C4"
Range("D16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R62C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R62C4"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R62C4"
Range("C17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R69C4"
Range("D17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R69C4"
Range("E17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R69C4"
Range("F17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R69C4"
Range("A1").Select

' FINLAND

Sheets("FI - Safetystatus").Select
' Ulykker med fravær - LTA
Range("C8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R6C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R6C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R6C4"
Range("F8").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R6C4"
Range("C9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R13C4"
Range("D9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R13C4"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R13C4"
Range("F9").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R13C4"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R20C4"
Range("D10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R20C4"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R20C4"
Range("F10").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R20C4"
Range("C11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R27C4"
Range("D11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R27C4"
Range("E11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R27C4"
Range("F11").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R27C4"
Range("C12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R34C4"
Range("D12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R34C4"
Range("E12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R34C4"
Range("F12").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R34C4"
Range("C13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R41C4"
Range("D13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R41C4"
Range("E13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R41C4"
Range("F13").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R41C4"
Range("C14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R48C4"
Range("D14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R48C4"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R48C4"
Range("F14").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R48C4"
Range("C15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R55C4"
Range("D15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R55C4"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R55C4"
Range("F15").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R55C4"
Range("C16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R62C4"
Range("D16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R62C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R62C4"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R62C4"
Range("C17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - DK'!R69C4"
Range("D17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - SE'!R69C4"
Range("E17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - NO'!R69C4"
Range("F17").Select
ActiveCell.FormulaR1C1 = "='Grunddata - FI'!R69C4"
Range("A1").Select

End Sub
 
B

Bob Bridges

Ack! There are numerous ways to make this a LOT shorter and easier to
maintain, netxel, and you're going to want to use them. My email address is
displayed in my profile; feel free to email me about turning all this into
just one or two score lines, but here are a few starting places:

Shorten the Run command line:

pfx = "'Safety status Nordic - new template 2009.xls'!"
Application.Run pfx & "Accidentwithlosttime"
Application.Run pfx & "Accidentwithmodifieddutyandinjuries"
Application.Run pfx & "Accidentwithoutlosttimebutpersonalinjuries"
...etc

Instead of Select and ActiveCell in two lines, combine in one:
Range("C8").FormulaR1C1 = "='Grunddata - DK'!R6C4"
Range("D8").FormulaR1C1 = "='Grunddata - SE'!R6C4"
Range("E8").FormulaR1C1 = "='Grunddata - NO'!R6C4"
Range("F8").FormulaR1C1 = "='Grunddata - FI'!R6C4"
...etc

Instead of handling rows 8 through 17 in 80 lines (or 40 after the above),
do them in a loop. This will take only 6 lines, if I'm not mistaken:

For jr = 8 to 17
Range("C" & jr).FormulaR1C1 = "='Grunddata - DK'!R" & (jr-7)*7-1 & "C4"
Range("D" & jr).FormulaR1C1 = "='Grunddata - SE'!R" & (jr-7)*7-1 & "C4"
Range("E" & jr).FormulaR1C1 = "='Grunddata - NO'!R" & (jr-7)*7-1 & "C4"
Range("F" & jr).FormulaR1C1 = "='Grunddata - FI'!R" & (jr-7)*7-1 & "C4"
Next jr

Instead of repeating all this code each time for four worksheets, call a
subroutine four times:

Sheets("DK - Safetystatus").Select
CopyGrundData
Sheets("NO - Safetystatus").Select
CopyGrundData
Sheets("SV - Safetystatus").Select
CopyGrundData
Sheets("FI - Safetystatus").Select
CopyGrundData

Those are some techniques for turning your long, weary subroutines into
something much shorter. Now, your question was about searching out a named
column: If you have a month name in a particular row, your program can take
the month name and find it in that row and then work on that column. Is that
what you want? I don't think it'll be very hard, but maybe I'd better find
out whether the above makes sense to you, first. If you can figure that out
(and as I said before, feel free to email me for more explanation), then the
next step is to look across the columns for the month name you want and go
from there. But maybe you'll need to explain where the month names are and
which sheet(s) these columns should be chosen from; I'm unclear on the
details.
 
Top