Excel/vba code

[mkostas66]

Hi!

I have the following code. It supposed to copy certain cells to a new book.
If i run the code in VBA with F5 it's working but when i hit the button with the code in the excel i get 0 copies.

Any ideas what's wrong?

Sub UpdateActionPlan()

Dim intAdded As Integer, intSRow, intTRow
Dim sngRecord(6)
Dim strTFullPath As String
Dim strText As String
Dim strResponse As VbMsgBoxResult
Dim cancel As Integer
Dim strFileName As String
Dim i As Integer
Dim LastRow As Integer
Dim FirstRow As Integer

FirstRow = 7
LastRow = 1000
i = FirstRow


strFileName = “Test.xls”
strTFullPath = strFileName
Workbooks.Open filename:=strTFullPath

Do Until i > LastRow
If Range("I" & i).Value = "Action" Then
 sngRecord(0) = Range("C" & i).Value
 sngRecord(1) = Range("G" & i).Value
 sngRecord(2) = Range("N" & i).Value
 sngRecord(3) = Range("O" & i).Value
 sngRecord(4) = Range("P" & i).Value
 sngRecord(5) = Range("T6").Value

 With Workbooks("Test.xlsx").Worksheets("CheckSheet")
       intTRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      .Range("C" & intTRow) = sngRecord(0)
      .Range("E" & intTRow) = sngRecord(1)
      .Range("G" & intTRow) = sngRecord(2)
      .Range("A" & intTRow) = sngRecord(3)
      .Range("D" & intTRow) = sngRecord(4)
     .Range("B" & intTRow) = sngRecord(5)
 End With
Else
 Cancel = True
End If

i = i + 1
Loop

End Sub

 

[igold]

Hi mkostar66- Welcome to the MrExcel Forum.
Are you sure your code is "connected" to your button. You may want to add a line like this as the first line of code to double-check...

MsgBox "Code is running"

 

[mkostas66]

Hi mkostar66- Welcome to the MrExcel Forum.
Are you sure your code is "connected" to your button. You may want to add a line like this as the first line of code to double-check...

MsgBox "Code is running"

It is connected because the target book is getting opened. It seems that it finds the last raw, selects the range and then doesn't copy the data...

 

[jkpieterse]

I wonder whether you're writing to the correct location. This line of code:
intTRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
does NOT take the row number from the workbook+worksheet listed in the With statement above it.
it needs a period in two places:
intTRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

 

[igold]

Have you tried stepping through the code using F8 to watch what is going on...

 

[mkostas66]

Have you tried stepping through the code using F8 to watch what is going on...

Yes, this works fine! The problem is when pressing the button, which it does nothing.
If i make the code for a single line it's working, the problem is with loop...

 

[mkostas66]

I wonder whether you're writing to the correct location. This line of code:
intTRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
does NOT take the row number from the workbook+worksheet listed in the With statement above it.
it needs a period in two places:
intTRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Before using this code, i've tried with a cell in the target group and count the rows. I've got the same problem.

 

[igold]

Yes, this works fine! The problem is when pressing the button, which it does nothing.

So if you put a stop in at the first line of the code and then use the button to trigger the code, your saying that the code fails, then when you step through the code- where does it fail. Do you have the Locals window open and are you watching it to see what is going on...

Another thought, what sheet is the Button on and what sheet are you on when you use F5... Try using F5 from the same sheet that the button is on...

 

[mkostas66]

So if you put a stop in at the first line of the code and then use the button to trigger the code, your saying that the code fails, then when you step through the code- where does it fail. Do you have the Locals window open and are you watching it to see what is going on...

Another thought, what sheet is the Button on and what sheet are you on when you use F5... Try using F5 from the same sheet that the button is on...

When I put the stop, there's no fail in step-through! When i run the code with the button i get the failure. I've also added a msgbox to show the copied items and in step by step I get 1 for each row. Pressing button displays 0 items copied and there's a selection of empty rows as range in the target book/sheet.

 

[jkpieterse]

What happens if you add DoEvents immediately after this line:
Workbooks.Open filename:=strTFullPath