Solved

Importing Survey Data

Posted on 2012-03-20
12
190 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 500 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now