Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Importing Survey Data

Posted on 2012-03-20
12
Medium Priority
?
212 Views
Last Modified: 2012-03-28
EE Professionals,

I have two Workbooks. The first WB is a "Survey".  The second WB is a Workbook where the imported data is analyzed.  I want to import a single Survey into the second WB for analysis (so no consolidation is needed; only copying from one WB to another).  The Survey WB will not change its name.  The second WB name will change from time to time.  For this reason, I think I need the macro to be in the second WB (vs. having an export routine in the Survey WB).  The numerical Data is the only data that needs to be imported and I'd like to use "range names" to copy and paste to the other book. I would assume that both WBs are open when the import takes place.

That's it!

Thank you,

B.
Worksheet2.xlsm
Survey-Worksheet.xlsm
0
Comment
Question by:Bright01
  • 6
  • 6
12 Comments
 
LVL 18

Expert Comment

by:p912s
ID: 37747934
Your updated workbook is attached. Range names were added to match the named ranges in the survey workbook.

The code was based on both workbooks being open at the same time.

HTH

Scot
Worksheet2.xlsm
0
 

Author Comment

by:Bright01
ID: 37754485
p912s,

Thanks for the response.... working on it tonight.  Question for you.  I will not have the same range names in the Worksheet that I have in the Survey WB.  I'm assuming if I put the Range Names in the Survey Workbook it will pull from those Ranges?  Or do I have to create them in the Target WB?

Thank you,

B.
0
 
LVL 18

Expert Comment

by:p912s
ID: 37754658
Hello!

You will need to create the range names in both the source and target workbooks. They don't need to be the same name, the code will need to have the correct names.

And the ranges of course need to be the same size - rows/columns for it to work correctly.

Scot
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 37754893
I'll give it a try.  Same Ranges, different Range names, one in each WB and then refer it correctly in the code.  Think I got it.

B.
0
 

Author Comment

by:Bright01
ID: 37765606
p912s,

An update and a troubleshooting issue;

I posted the code in the destination WS (i.e. Governance Workshop), after naming all of the range names in both the Source and Destinations.  I get a debug error on line:

Windows(dest).Activate

I think I may not have the src, dest and sht labels right.  Can you help with specifying which are required?  It looked like I needed to name both Workbooks, but only the source worksheet...... is that right?  Don't I need to tell it the target worksheet or does it pick that up via the range name?

Here's the current code;



Sub ImportDatafromSurvey()
    Dim src As String
    Dim dest As String
    Dim sht As String
 
    src = "Governance_Survey.xlsm"
    dest = "Governance_Workshop.xlsm"
    sht = "Survey"
 
    'copy range1
    Windows(src).Activate
    Worksheets(sht).Range("Category1").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category1Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range2
    Windows(src).Activate
    Worksheets(sht).Range("Category2").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category2Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range3
    Windows(src).Activate
    Worksheets(sht).Range("Category3").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category3Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range4
    Windows(src).Activate
    Worksheets(sht).Range("Category4").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category4Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
   
   'copy range5
    Windows(src).Activate
    Worksheets(sht).Range("Category5").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category5Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range6
    Windows(src).Activate
    Worksheets(sht).Range("Category6").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category6Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range7
    Windows(src).Activate
    Worksheets(sht).Range("Category7").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category7Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range8
    Windows(src).Activate
    Worksheets(sht).Range("Category8").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category8Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range9
    Windows(src).Activate
    Worksheets(sht).Range("Category9").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category9Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range10
    Windows(src).Activate
    Worksheets(sht).Range("Category10").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category10Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range11
    Windows(src).Activate
    Worksheets(sht).Range("Category11").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category11Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
   
    'copy range12
    Windows(src).Activate
    Worksheets(sht).Range("Category12").Copy
    Windows(dest).Activate
    Worksheets(sht).Range("Category12Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
           
    'park in a1
    Cells(1, 1).Select
End Sub
0
 
LVL 18

Expert Comment

by:p912s
ID: 37766746
>>I get a debug error on line:
>>Windows(dest).Activate

What is the error?


>>dest = "Governance_Workshop.xlsm"

Is that thename of the workbook? Spelling? Underscore?


 >> sht = "Survey"

Is this the name of the sheet in both workbooks?
0
 

Author Comment

by:Bright01
ID: 37767098
"Runtime error 9 -- Script out of range"

This is why I think I have not put in the right source targets.

So...... the Survey (where the data is coming from is = Governance_Survey (this is the Workbook) and the Worksheet is "Survey".  The destination (where the data is being imported to) is called Governance_Workbenchv10 and the worksheet is the Governance_Questionaire.

One challenge we may have is do I have to identify the destination WB?  The reason is because each time I improve it, I version it to the next level.

Does that answer your question?

B.
0
 
LVL 18

Accepted Solution

by:
p912s earned 2000 total points
ID: 37769574
Place this in your destination workbook. This will allow for you to SaveAs changing the name on the target alfter update.

Let me know how it goes.

Scot

Sub ImportDatafromSurvey()
    Dim src As String
    Dim dest As String
    Dim sht As String
 
    src = "Governance_Survey.xlsm"
    dest = ActiveWorkbook.Name
    sht1 = "Survey"
    sht2 = "Governance_Questionaire"
 
    'copy range1
    Windows(src).Activate
    Worksheets(sht1).Range("Category1").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category1Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range2
    Windows(src).Activate
    Worksheets(sht1).Range("Category2").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category2Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range3
    Windows(src).Activate
    Worksheets(sht1).Range("Category3").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category3Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range4
    Windows(src).Activate
    Worksheets(sht1).Range("Category4").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category4Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    
   'copy range5
    Windows(src).Activate
    Worksheets(sht1).Range("Category5").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category5Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range6
    Windows(src).Activate
    Worksheets(sht1).Range("Category6").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category6Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range7
    Windows(src).Activate
    Worksheets(sht1).Range("Category7").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category7Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range8
    Windows(src).Activate
    Worksheets(sht1).Range("Category8").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category8Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range9
    Windows(src).Activate
    Worksheets(sht1).Range("Category9").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category9Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range10
    Windows(src).Activate
    Worksheets(sht1).Range("Category10").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category10Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range11
    Windows(src).Activate
    Worksheets(sht1).Range("Category11").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category11Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'copy range12
    Windows(src).Activate
    Worksheets(sht1).Range("Category12").Copy
    Windows(dest).Activate
    Worksheets(sht2).Range("Category12Questionaire").Select
    Selection.PasteSpecial Paste:=xlPasteValues
            
    'park in a1
    Cells(1, 1).Select
End Sub

Open in new window

0
 

Author Comment

by:Bright01
ID: 37770882
Scot,

I still get a compile error.  It doesn't see sht1 and sht2 as "defined".  I added the following;

Dim sht1 As String
Dim sht2 As String

And that got me around that problem.  

Next, I got a "script out of range" error on this line:


Worksheets(sht2).Range("Category1Questionaire").Select


Any ideas?

B.
0
 
LVL 18

Expert Comment

by:p912s
ID: 37770901
Glad you caught the Dim statements.

>>sht2 = "Governance_Questionaire"
Is that the name of the sheet in your target workbook?

>>Worksheets(sht2).Range("Category1Questionaire").Select
Is that range defined in the target workbook?
0
 

Author Closing Comment

by:Bright01
ID: 37775949
Scott,

Much thanks!  Got it to work in my WB.  Very cool!

Appreciate the help/assistance.

B.
0
 
LVL 18

Expert Comment

by:p912s
ID: 37776033
You're welcome, glad it worked.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question