PDA

View Full Version : Need to create VBA to transfer data in between workbooks



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?

SamT
07-24-2013, 07:24 AM
DragonBone,

Welcome to VBAExpress, I'm sorry it took so long to get back to you.

I'm showing this line to illustrate the VBA sytaxk errors in your code. BTW, that's a lot of code for a beginner to write. Compare this to what you have.

Range("A2").Copy Destination:=Workbooks("ELIGIBILITY_FILE_MACRO.xlsm").Range("B2")

Here is some incomplete code that will give you a good idea of how to write the rest of your code. If you can't get it to work with this small hint, please come back with specific questions. We love to help people learn VBA.


Option Explicit
'Always put "Option Explicit" at the top of all code

Sub SamT()
Dim Health As Workbook
Dim Eligiblility As Workbook

Set Health = Workbooks("health-savr-4macro.csv")
Set Eligiblility = Workbooks("ELIGIBILITY_FILE_MACRO.xlsm")

'Specify the books by variable
'"Destination:=" is implied when no "Copy" parameters are specified

Health.Range("A2").Copy Eligiblility.Range("B2")
Health.Range("B2").Copy Eligiblility.Range("D2")
Health.Range("C2").Copy Eligiblility.Range("F2")
Health.Range("D2:H2").Copy Eligiblility.Range("I2:M2")
'Continue with this pattern

End Sub