Error's want to help

[KK1966]

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



[VBA]
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
[/VBA]

[mdmackillop]

You are selecting the same cell for pasted values. Your code should be something like
[VBA]
With home.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
.Select 'for debugging
[/VBA]

[KK1966]

Dear Mdmackillop

Very thanks your help,
additional one if I wanto to change the formulas to copy form the worksheets K2 to K13 how's it change?

Thanks
Very much

[mdmackillop]

[VBA]For k = 2 To 13
With home.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
.Formula = "='" & myDir & "[" & fn & "]Sheet1'!K" & k
.Value = .Value
COLCOUNT = COLCOUNT + 1
End With
Next[/VBA]

[KK1966]

Dear Mdmackillop


The code seems copy from K to left counted....

might I message not clear,
I means to want copy form file Range (K2 to K13) then paste in my objects sheets...

would you mind help again, thanks very much

[mdmackillop]

I'm not clear where data is to be pasted. Can you show this on a sample workbook and post it?

[KK1966]

Thanks Your help


Format same as attached & which file is by daily.

[mdmackillop]

[VBA]Sub testing()
Dim home As Worksheet
Dim FNname As String
Dim Wb As Workbook
Dim Tgt As Range

Set home = ActiveSheet

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show = -1 Then
'Get target for paste
Set Tgt = home.Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
'Open Source file
FName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If FName <> False Then
Set Wb = Workbooks.Open(FName)
'Copy and PasteSpecial values
Wb.Worksheets("MAN_SUM").Range("H13:H16").Copy
Tgt.PasteSpecial xlPasteValues
'Close Source wihout saving
Wb.Close False
End If
End If
End With
End Sub
[/VBA]

[KK1966]

Dear Mdmackillop

Your code is perfectly.

Thanks very much.

[lucas]

Perfect example of why you should post an example workbook. It would have saved Malcolm quite a bit of time trying to figure out what you were trying to do.