PDA

View Full Version : Error's want to help



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

mdmackillop
06-12-2008, 12:28 AM
You are selecting the same cell for pasted values. Your code should be something like

With home.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
.Select 'for debugging

KK1966
06-12-2008, 12:57 AM
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
06-12-2008, 01:28 AM
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

KK1966
06-12-2008, 01:38 AM
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
06-12-2008, 02:27 AM
I'm not clear where data is to be pasted. Can you show this on a sample workbook and post it?

KK1966
06-12-2008, 02:35 AM
Thanks Your help


Format same as attached & which file is by daily.

mdmackillop
06-12-2008, 04:33 AM
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

KK1966
06-12-2008, 07:55 AM
Dear Mdmackillop

Your code is perfectly.

Thanks very much.

lucas
06-12-2008, 08:10 AM
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.:whip