dragonbone
07-17-2013, 05:25 PM
I need to create a VBA to transfer data in specific cells in one row to up to six rows on a master macro enabled workbook. I am using Excel 2010. what I have figured out so far is:
Windows("ELIGIBILITY_FILE_MACRO.xlsm").Activate
Range("B2").Select
Windows("health-savr-4macro.csv").Activate
Range("A2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B2")
Range("B2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D2")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F2")
Range("D2:H2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("I2:M2")
Range("I2:J2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("O2:P2")
Range("AL2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("R2")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T2")
Range("K2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U2")
Range("L2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y2")
if there is data in Windows("ELIGIBILITY_FILE_MACRO.xlsm")("B2"), then Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g2)=00
**if there is data in Windows("health-savr-4macro.csv") (N2) do the following**
Range("N2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B3")
Range("O2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D3")
Range("P2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U3")
Range("Q2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y3")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F3")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T3")
if data in Windows("health-savr-4macro.csv")("N2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g3)=01
**if there is data in Windows("health-savr-4macro.csv") (Q2) do the following**
Range("Q2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B4")
Range("R2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D4")
Range("S2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U4")
Range("T2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y4")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F4")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T4")
if data in Windows("health-savr-4macro.csv")("Q2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g4)=02
**if there is data in Windows("health-savr-4macro.csv") (V2) do the following**
Range("V2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B5")
Range("W2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D5")
Range("X2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U5")
Range("Y2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y5")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F5")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T5")
if data in Windows("health-savr-4macro.csv")("V2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g5)=03
**if there is data in Windows("health-savr-4macro.csv") (Z2) do the following**
Range("Z2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B6")
Range("AA2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D6")
Range("AC2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U6")
Range("AB2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y6")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F6")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T6")
if data in Windows("health-savr-4macro.csv")("Z2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g6)=04
**if there is data in Windows("health-savr-4macro.csv") (AD2) do the following**
Range("AD2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B7") (first)
Range("AE2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D7")
Range("AF2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U7")
Range("AG2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y7")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F7")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T7")
if data in Windows("health-savr-4macro.csv")("AD2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g7)=05
**if there is data in Windows("health-savr-4macro.csv") (AH2) do the following**
Range("AH2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B8")
Range("AI2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D8")
Range("AJ2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U8")
Range("AK2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y8")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F8") Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T8")
if data in Windows("health-savr-4macro.csv")("AH2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g8)=05
Then move to row2 in Windows("health-savr-4macro.csv") and repeat until there is no more data
How do I write this so it works, starting at the first row of the downloaded document, and fills in rows on the macro document?
Windows("ELIGIBILITY_FILE_MACRO.xlsm").Activate
Range("B2").Select
Windows("health-savr-4macro.csv").Activate
Range("A2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B2")
Range("B2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D2")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F2")
Range("D2:H2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("I2:M2")
Range("I2:J2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("O2:P2")
Range("AL2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("R2")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T2")
Range("K2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U2")
Range("L2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y2")
if there is data in Windows("ELIGIBILITY_FILE_MACRO.xlsm")("B2"), then Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g2)=00
**if there is data in Windows("health-savr-4macro.csv") (N2) do the following**
Range("N2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B3")
Range("O2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D3")
Range("P2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U3")
Range("Q2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y3")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F3")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T3")
if data in Windows("health-savr-4macro.csv")("N2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g3)=01
**if there is data in Windows("health-savr-4macro.csv") (Q2) do the following**
Range("Q2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B4")
Range("R2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D4")
Range("S2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U4")
Range("T2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y4")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F4")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T4")
if data in Windows("health-savr-4macro.csv")("Q2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g4)=02
**if there is data in Windows("health-savr-4macro.csv") (V2) do the following**
Range("V2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B5")
Range("W2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D5")
Range("X2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U5")
Range("Y2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y5")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F5")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T5")
if data in Windows("health-savr-4macro.csv")("V2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g5)=03
**if there is data in Windows("health-savr-4macro.csv") (Z2) do the following**
Range("Z2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B6")
Range("AA2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D6")
Range("AC2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U6")
Range("AB2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y6")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F6")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T6")
if data in Windows("health-savr-4macro.csv")("Z2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g6)=04
**if there is data in Windows("health-savr-4macro.csv") (AD2) do the following**
Range("AD2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B7") (first)
Range("AE2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D7")
Range("AF2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U7")
Range("AG2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y7")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F7")
Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T7")
if data in Windows("health-savr-4macro.csv")("AD2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g7)=05
**if there is data in Windows("health-savr-4macro.csv") (AH2) do the following**
Range("AH2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("B8")
Range("AI2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("D8")
Range("AJ2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("U8")
Range("AK2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("Y8")
Range("C2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("F8") Range("M2").Copy Destination:=Windows("ELIGIBILITY_FILE_MACRO.xlsm")Range-("T8")
if data in Windows("health-savr-4macro.csv")("AH2"), Windows("ELIGIBILITY_FILE_MACRO.xlsm")(g8)=05
Then move to row2 in Windows("health-savr-4macro.csv") and repeat until there is no more data
How do I write this so it works, starting at the first row of the downloaded document, and fills in rows on the macro document?