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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.