KK1966
06-11-2008, 11:54 PM
Daer Experts
The intents of the code is wanted when getting external data to paste in my object WS Col A” and if “COL A have values then automatically to paste in the Col “B …Etc to across from left to right column , base the header of Date / Month to go.
But it can’t be work :
Can u help to modify or learn me about what’s was it wrong. Thanks very much
Sub test()
Dim home As Worksheet
Dim Filename As String, myDir As String, fn As String
Dim COLCOUNT As Long
COLCOUNT = 0
Set home = ThisWorkbook.ActiveSheet
COLCOUNT = 1 + 1
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show = -1 Then
Filename = .SelectedItems(1)
myDir = Left$(Filename, InStrRev(Filename, "\"))
fn = Mid$(Filename, InStrRev(Filename, "\") + 1)
With home.Cells(COLCOUNT + 1).End(xlUp)(1).Resize(1)
.Formula = "='" & myDir & "[" & fn & "]Sheet1'!K6"
.Value = .Value
COLCOUNT = COLCOUNT + 1
End With
End If
End With
End Sub
The intents of the code is wanted when getting external data to paste in my object WS Col A” and if “COL A have values then automatically to paste in the Col “B …Etc to across from left to right column , base the header of Date / Month to go.
But it can’t be work :
Can u help to modify or learn me about what’s was it wrong. Thanks very much
Sub test()
Dim home As Worksheet
Dim Filename As String, myDir As String, fn As String
Dim COLCOUNT As Long
COLCOUNT = 0
Set home = ThisWorkbook.ActiveSheet
COLCOUNT = 1 + 1
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show = -1 Then
Filename = .SelectedItems(1)
myDir = Left$(Filename, InStrRev(Filename, "\"))
fn = Mid$(Filename, InStrRev(Filename, "\") + 1)
With home.Cells(COLCOUNT + 1).End(xlUp)(1).Resize(1)
.Formula = "='" & myDir & "[" & fn & "]Sheet1'!K6"
.Value = .Value
COLCOUNT = COLCOUNT + 1
End With
End If
End With
End Sub