?
Solved

Opening file to copy columns

Posted on 2014-02-04
4
Medium Priority
?
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

762 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