Creating a variable and pickout data
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'!Accidentwithoutlosttimebutpersonalinjuri es" 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 |
Creating a variable and pickout data
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. --- "netxel" wrote: Could anybody tell me how to write a macro where the Colum is chosen by name? The rows are always the same, but I would like 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. Sub JanuarNORDIC() ' JanuarNORDIC Makro ' Makro indspillet 16-02-2010 af tue.madsen ' Application.Run pfx & "Accidentwithlosttime" Application.Run pfx & "Accidentwithmodifieddutyandinjuries" Application.Run pfx & "Accidentwithoutlosttimebutpersonalinjuries" Application.Run pfx & "Accidentwithmaterialdamageds" Application.Run pfx & "Accidentsaccumulatedfortheyear" Application.Run pfx & "Nearmissanddangerussituations" Application.Run pfx & "NearmissAccumulatedForTheYear" Application.Run pfx & "AccidentFrequency" Sheets("DK - Safetystatus").Select Range("A1").Select End Sub ----------------------------------------------------------------------------------- ' Makro indspillet 16-02-2010 af tue.madsen ' Ulykker med fravær - LTA Sub Accidentwithlosttime() ' DANMARK Sheets("DK - Safetystatus").Select CopyGrund ' SVERIGE Sheets("SV - Safetystatus").Select CopyGrund ' NORGE Sheets("NO - Safetystatus").Select CopyGrund ' Finlândia Sheets("FI - Safetystatus").Select CopyGrund End Sub ----------------------------------------------------------------------------------- ' Copy grunddata here Sub CopyGrund() 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 Range("A1").Select End Sub |
All times are GMT +1. The time now is 03:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com