98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Bringing in data from a separate workbook | Excel forum
Bringing in data from a separate workbook | Excel forum
Resolved · Urgent Priority · Version 2003
Mon 18 Oct 2010, 16:59Delegate Samantha said...
Samantha has attended:
Excel VBA Intro Intermediate course
Bringing in data from a separate workbook
How can I write a code to bring in particular data based on if it has information populated or not from a separate workbook into a specific cell.
The code that I currently have is:
Option Explicit
Dim intColumnCount As Integer
Dim intRowCount As Integer
Dim intTargetRowCount As Integer
Dim strActiveSheet As String
Dim rngDestination As Range
Sub GetData()
'intTargetRowCount = 2 'where data will go
'Windows("ASPT CBv9.xls").Activate
Sheets("Sheet1").Cells(2, 1).Value = _
Sheets("= '[ASPT CBv9.xls]1011 Budget'!R15C8").Value '.Range("H15").Value
'Sheets("1011 Budget").Range("H15").Select
'For intRowCount = 1 To Sheets("1011 Budget").Range("H15").CurrentRegion.Rows.Count
'Sheets("Sheet1").Cells(intTargetRowCount, 1).Value = _
Sheets("1011 Budget").Range("H15").Value 'Cells(intRowCount, 1).
'intTargetRowCount = intTargetRowCount + 1
'Next intRowCount
End Sub
Wed 20 Oct 2010, 11:20Trainer Stephen said...
RE: Bringing in data from a separate workbook
Hi Samantha
Thanks for your question
Can you clarify first what happens when you run the code above. Does it crash, and if so at what line?
Thanks
Stephen
Wed 20 Oct 2010, 17:14Delegate Samantha said...
RE: Bringing in data from a separate workbook
Hi Stephen,
We've actually managed to amend the code to bring the data in now, but it's not bringing it in as expected - the problem is that there are two issues:
The first is that the data is being returned into Row 1 instead of Row 2 as requested by the code
The second is that the second round of data is over-riding the first round of data and we can't get it to put the data underneath the first lot of data retrieved.
We're not getting any error codes as such - just that the information is not being pulled through as anticipated from the seperate workbook.
The amended code is as follows:
Option Explicit
Dim intColumnCount As Integer
Dim intRowCount As Integer
Dim intTargetRowCount As Integer
Dim strActiveSheet As String
Dim rngDestination As Range
Dim strBudgetName As String
Dim intZero As Integer
Sub Get_Data_from_within_workbook()
intZero = 0
intTargetRowCount = 2 'Not making any difference
For intRowCount = 1 To Sheets("1011 Budget").Range("H15").CurrentRegion.Rows.Count
If Sheets("1011 Budget").Range("E15").Cells(intRowCount, 1).Value > intZero Then
For intColumnCount = 1 To 2
Sheets("Sheet1").Cells(intRowCount, intColumnCount).Value = _
Sheets("1011 Budget").Range("H15").Cells(intRowCount, intColumnCount).Value
Next intColumnCount
intTargetRowCount = intTargetRowCount + 1
End If
Next intRowCount
For intRowCount = 1 To Sheets("1011 Budget").Range("H53").CurrentRegion.Rows.Count
If Sheets("1011 Budget").Range("E53").Cells(intRowCount, 1).Value > intZero Then
For intColumnCount = 1 To 2
Sheets("Sheet1").Cells(intRowCount, intColumnCount).Value = _
Sheets("1011 Budget").Range("H53").Cells(intRowCount, intColumnCount).Value
Next intColumnCount
intTargetRowCount = intTargetRowCount + 1
End If
Next intRowCount
For intRowCount = 1 To Sheets("1011 Budget").Range("E12").CurrentRegion.Rows.Count
If Sheets("1011 Budget").Range("E12").Cells(intRowCount, 1).Value > intZero Then
For intColumnCount = 3 To 3
Sheets("Sheet1").Cells(intRowCount, intColumnCount).Value = _
Sheets("1011 Budget").Range("E12").Cells(intRowCount, intColumnCount).Value
Next intColumnCount
intTargetRowCount = intTargetRowCount + 1
End If
Next intRowCount
End Sub
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Counting Non Number Cells (Text)If you try to use the COUNT FUNCTION =COUNT(Cell range)with a range of cells with numbers and or containing text fields you wil find that that the text cells will be excluded from the the count. If you want to include them try the the COUNTA FUNCTION =COUNTA(Cell range). This counts both text and number cell values. |
