VBa code open closed file and copy columns working but nothing doing

Question

I have a code which should open input file and copy from there some specific columns. This macro not showing any bug its working but nothing happend, i dont see any action. I have Excel 2016

Sub btnExport_Click()
Dim strPath As String
Dim wbMe, wb As Workbook

strPath = selectFile
If strPath = "" Then Exit Sub

Set wbMe = ActiveWorkbook

Set wb = Workbooks.Open(strPath, False, True)
wb.Sheets(1).Columns("A:C").Copy Destination:=wbMe.Sheets(1).Range("A1")
wb.Sheets(1).Columns("H").Copy Destination:=wbMe.Sheets(1).Range("D1")

wb.Close False
Set wb = Nothing

Beep
MsgBox "The data was imported"
End Sub
Private Function selectFile()
Dim fd As Office.FileDialog

Set fd = Application.FileDialog(3)


With fd
.InitialFileName = ActiveWorkbook.Path
.AllowMultiSelect = False
.Title = "Please select file to import."
.Filters.Clear
.Filters.Add "Excel", "*.xlsm"

If Show = True Then selectFile = .SelectedItems(1)

End With
End Function

have you tried witth `Set wbMe = ThisWorkbook`? Also, notice that `Dim wbMe, wb As Workbook` will define only `wb` as Workbook, but `wbMe` will be defined as Variant. It should be `Dim wbMe as Workbook, wb As Workbook` — Foxfire And Burns And Burns, Sep 16 '21 at 10:43
you can reuse the codesnippet for copyRangeValues I provided in https://stackoverflow.com/a/69183390/16578424 Also be aware that ActiveWorkbook might not be the one you want - use Thisworkbook instead (it's the workbook the code resides in) — Ike, Sep 16 '21 at 10:43
@Foxfire And Burns And Burns i changed Dim wbMe As Workbook, wb As Workbook Set wbMe = ThisWorkbook .InitialFileName = ThisWorkbook.Path and nothing happend still — Przemek Dabek, Sep 16 '21 at 10:54

score 2 · Accepted Answer · answered Sep 16 '21 at 11:03

This is in your worksheet module:

Option Explicit

Sub btnExport_Click()
Dim strPath As String
Dim wbMe As Workbook, wb As Workbook

strPath = selectFile

If strPath = "" Then Exit Sub

Set wbMe = ActiveWorkbook
Set wb = Workbooks.Open(strPath, False, True)

copyRangeValues wb.Sheets(1).Columns("A:C"), wbMe.Sheets(1).Range("A1")
copyRangeValues wb.Sheets(1).Columns("H"), wbMe.Sheets(1).Range("D1")

wb.Close False
Set wb = Nothing

Beep
MsgBox "The data was imported"
End Sub

Add a module to your project if you haven't yet.

Paste the following functions to that module

Option explicit

Public Function selectFile()

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(3)

With fd
    .InitialFileName = ActiveWorkbook.Path
    .AllowMultiSelect = False
    .Title = "Please select file to import."
    .Filters.Clear
    .Filters.Add "Excel", "*.xlsm"
    
    If Show = True Then selectFile = .SelectedItems(1)
End With

End Function


Public Sub copyRangeValues(rgSource As Range, rgTargetCell As Range)
'generic routine to copy one range to another
'rgTargetCell = top left corner of target range

Dim rgTarget As Range
'resize rgTarget according to dimensions of rgSource
With rgSource
    Set rgTarget = rgTargetCell.Resize(.Rows.Count, .Columns.Count)
End With


'write values from rgSource to rgTarget - no copy/paste necessary!!!
'formats are not copied - only values
rgTarget.value = rgSource.value

End Sub

Thanks a lot, Could you tell me also how i can add some code part to select one tabs frome file where i have many tabs avaliable only from the file input which i would like select — Przemek Dabek, Sep 16 '21 at 12:04

VBa code open closed file and copy columns working but nothing doing

1 Answers1