Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Creating a variable and pickout data
 
 
LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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
Reply With QuoteReply With Quote
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help creating a sum based on variable data. John McKeon New Users to Excel 1 May 7th 10 09:38 PM
Creating a chart that has a variable data ranges? CattleDr Excel Programming 1 August 6th 07 11:07 PM
I need your help Dear Vba Guru's..."Creating range in a variable data.." [email protected] Excel Programming 2 January 23rd 07 08:01 AM
creating a variable Abe New Users to Excel 1 April 15th 06 06:41 AM
Macro Creating Variable and using variable in a SQL statement Jimmy Excel Programming 4 October 25th 04 02:36 AM


All times are GMT +1. The time now is 01:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement