Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Opening file to copy columns

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
leezac
Asked:
leezac
  • 2
  • 2
1 Solution
 
byundtCommented:
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
 
leezacAuthor Commented:
Thanks but am getting an error on this line


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


VBA error 1004 - select method of range class failed
0
 
byundtCommented:
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
 
leezacAuthor Commented:
I see - thanks
0

Featured Post

Technology Partners: 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!

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