Solved

Opening file to copy columns

Posted on 2014-02-04
4
197 Views
Last Modified: 2014-02-05
I need help when I open the workbook to copy columns]]
   on this line.
   Workbooks.Open(DIV).Activate  ''>>>>>>>>>>>>>What do I need to place here to reopen workbook??????????????????
The excel file that is opened will have a different name each time - so used DIV.


VAR_DIV = DIV ----------VAR_DIV is a text box



Thanks in advance

Sub SELECT_Div()
Dim DIV As String
 Dim Lastrow As Integer

    DIV = Application.GetOpenFilename(FileFilter:="xls*.Files (*.xls*), *.xls*", Title:="Please select a file")
   
    If DIV = "" Then
        MsgBox "No File Selected"
            Exit Sub
        Else
    End If
      'browse boxes may be on the main parm sheet i.e. Sheets

     Sheets("Variances").VAR_DIV = DIV  'sheet has text box VAR_DIV
 Workbooks.Open (DIV)
         
   ' Cells.Copy
       Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
 
     Range("A4:E & Lastrow").Select
    Selection.Copy
    Windows("Template.xlsm").Activate
    Range("B3").Select
    ActiveSheet.Paste
   
   Workbooks.Open(DIV).Activate  ''>>>>>>>>>>>>>What do I need to place here to reopen workbook??????????????????

Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
 
    Range("G4:G7 & Last row").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Template.xlsm").Activate
     Range("L3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
End Sub
0
Comment
Question by:leezac
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39834309
Note how I set a workbook variable to DIV when you open it. You can then refer to wb when you want to close that workbook or activate it.
Sub SELECT_Div()
Dim DIV As String
Dim wb As Workbook            'Brad added this statement
Dim Lastrow As Integer

    DIV = Application.GetOpenFilename(FileFilter:="xls*.Files (*.xls*), *.xls*", Title:="Please select a file")
    
    If DIV = "" Then
        MsgBox "No File Selected"
            Exit Sub
        Else
    End If
      'browse boxes may be on the main parm sheet i.e. Sheets

     Sheets("Variances").VAR_DIV = DIV  'sheet has text box VAR_DIV
Set wb = Workbooks.Open(DIV)             'Brad changed this statement
         
   ' Cells.Copy
       Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 
     Range("A4:E & Lastrow").Select
    Selection.Copy
    Windows("Template.xlsm").Activate
    Range("B3").Select
    ActiveSheet.Paste
   
   wb.Activate  'Brad changed this statement to use a workbook variable to point to DIV

Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 
    Range("G4:G7 & Last row").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Template.xlsm").Activate
     Range("L3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
End Sub

Open in new window

0
 

Author Comment

by:leezac
ID: 39835600
Thanks but am getting an error on this line


Range("A4:E & Lastrow").Select


VBA error 1004 - select method of range class failed
0
 
LVL 81

Expert Comment

by:byundt
ID: 39835616
That line should have been:
Range("A4:E" & Lastrow).Select

Note how the last doublequote moved.

A similar problem occurs on statement 31, which should read:
Range("G4:G" & Lastrow).Select
0
 

Author Comment

by:leezac
ID: 39835899
I see - thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 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

18 Experts available now in Live!

Get 1:1 Help Now