PDA

View Full Version : Can not run VBA use data from other excel file



hitmen
02-19-2014, 02:25 AM
Hi everyone,

I want to use data from other excel file in folder, but this code can not run (sheet CP01, CP02... automatic update), this is code:



Sub TH()
Dim Wb, WTH As Workbook
Dim ws As Worksheet
Dim i As Integer

For i = 1 To [F2].Value
On Error Resume Next
Set WTH = Workbooks("Analysis.xls")
Set ws = WTH.Sheets("Basic").Range("C" & i + 1)
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\Report human_" & WTH.Sheets("Basic").Range("C" & i + 1).Value & ".xls")

'auto find sheets
WTH.Sheets([WTH.Sheets("Basic").Range("C" & i + 1).Value]).Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value
' WTH.Sheets("CP01").Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value

Wb1.Close False

Next i

End Sub

Pls: see attack file

Bob Phillips
02-19-2014, 03:15 AM
What do you mean by ... this code can not run (sheet CP01, CP02... automatic update)

hitmen
02-19-2014, 07:35 PM
Hi xld,

I mean sheet CP01,CP02, CP03... will automatic input data when file excel "Report human_" CP01, CP02, CP03... open.

I know this code ("WTH.Sheets([WTH.Sheets("Basic").Range("C" & i + 1).Value]).Range("A2:D15").Value") not correct.

Thank you

hitmen
02-20-2014, 08:04 PM
Hi every one,

Pls reply help me, I have 5 file in 1 folder include: (Analysis.xls; Report human_CP01.xls; Report human_CP02.xls....)
When click Button 1 in sheet Basic in File Analysis.xls, sheets CP01, CP02... auto update

westconn1
02-21-2014, 02:35 AM
try this to see if it works as you want

Sub TH()
Dim Wb1 As Workbook, WTH As Workbook
Dim ws As Worksheet
Dim i As Integer

Set WTH = Workbooks("Analysis.xls")
For i = 1 To [F2].Value
Set cel = WTH.Sheets("Basic").Range("C" & i + 1)
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\Report human_" & cel & ".xls")

'auto find sheets
WTH.Sheets(cel).Range("A2:d15").Value = Wb1.Sheets("Total").Range("A2:D15").Value
' WTH.Sheets("CP01").Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value

Wb1.Close False

Next i

End Sub

hitmen
02-21-2014, 02:59 AM
try this to see if it works as you want

Sub TH()
Dim Wb1 As Workbook, WTH As Workbook
Dim ws As Worksheet
Dim i As Integer

Set WTH = Workbooks("Analysis.xls")
For i = 1 To [F2].Value
Set cel = WTH.Sheets("Basic").Range("C" & i + 1)
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\Report human_" & cel & ".xls")

'auto find sheets
WTH.Sheets(cel).Range("A2:d15").Value = Wb1.Sheets("Total").Range("A2:D15").Value
' WTH.Sheets("CP01").Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value

Wb1.Close False

Next i

End Sub




Hi,

The sheet CP01, CP02... not active. and have noted TYPE MISMATCH,

westconn1
02-21-2014, 03:15 AM
and have noted TYPE MISMATCH,on which line?
The sheet CP01, CP02... not activeplease explain

hitmen
02-21-2014, 03:28 AM
HI,

Visual Basic note "TYPE MISMATCH"

The sheet CP01, CP02... not copy data from sheets "Total" file Report human_01, Report human_02...

(code not run)

hitmen
02-21-2014, 03:57 AM
on which line?please explain

Hi everyone,

I can write:
Sub TH()
Dim Wb1 As Workbook, WTH As Workbook
Dim ws As Worksheet
Dim i As Integer

Set WTH = Workbooks("Analysis.xls")

Set cel = WTH.Sheets("Basic").Range("C2")
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\Report human_" & cel & ".xls")
WTH.Sheets("CP01").Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value
Wb1.Close False


Set cel = WTH.Sheets("Basic").Range("C3")
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\Report human_" & cel & ".xls")
WTH.Sheets("CP02").Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value
Wb1.Close False

Set cel = WTH.Sheets("Basic").Range("C4")
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\Report human_" & cel & ".xls")
WTH.Sheets("CP03").Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value
Wb1.Close False

Set cel = WTH.Sheets("Basic").Range("C5")
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\Report human_" & cel & ".xls")
WTH.Sheets("CP04").Range("A2:D15").Value = Wb1.Sheets("Total").Range("A2:D15").Value
Wb1.Close False
End Sub

But, it is not pro

westconn1
02-21-2014, 04:15 AM
change this line, where error occurs
WTH.Sheets(cel.Value).Range("A2:d15").Value = Wb1.Sheets("Total").Range("A2:D15").Value

hitmen
02-21-2014, 04:48 AM
change this line, where error occurs
WTH.Sheets(cel.Value).Range("A2:d15").Value = Wb1.Sheets("Total").Range("A2:D15").Value


Perfect,

Thanks you so much