• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

Importing Survey Data

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
Bright01
Asked:
Bright01
  • 6
  • 6
1 Solution
 
p912sCommented:
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
 
Bright01Author Commented:
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
 
p912sCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Bright01Author Commented:
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
 
Bright01Author Commented:
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
 
p912sCommented:
>>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
 
Bright01Author Commented:
"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
 
p912sCommented:
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
 
Bright01Author Commented:
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
 
p912sCommented:
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
 
Bright01Author Commented:
Scott,

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

Appreciate the help/assistance.

B.
0
 
p912sCommented:
You're welcome, glad it worked.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now