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
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
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
