Command button and run/sub user form button not in sync

Question

on my excel sheet, I have a command button (GrabMyData) which grabs, copies data onto a workbook (thisWB). Now when I press this command button, the data appears on the 'top rows' of the column. However, on the excel VBA editor, when I press the run/sub userform play button, the data comes to the 'last rows' of data on the column, which is what I want. So, why is my command button not doing what the run/sub play button is doing? I thought they both do the same function. Please note, I have correctly assigned the macro command button to the relevant work book. Please advise.

Sub GrabMyData()
  Dim myWB As Workbook
  Dim thisWB As Workbook
  Dim thisWS As Worksheet
  Dim lastRow As String

  Application.ScreenUpdating = False

  Set myWB =  Workbooks.Open("C:\Users\jjordan\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\GTER232\TEST.csv")

  myWB.Sheets("Sheet 1").Range("A8:F17").Copy

  Set thisWB = ThisWorkbook
  Set thisWS = ThisWorkbook.Sheets("Sheet 5")

  thisWB.Activate

  lastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row

  thisWS.Range("H" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
  End With

 myWB.Close


End Sub

So the destination is different, right? Looks like in the code the destination is computed as lastRow, right? Can you try using a debug print on the value, so Debug.Print lastRow? Also do you need to use ActiveSheet, I'd pin that sucker down with a name so ThisWorkbook.Worksheets("foo"). — S Meaden
Yes the destination is different. Yes, its is computed as the lastRow, as I want the imported data to appear at the last row within the column range and not at the top rows in the column range. So, I presume the run/sub user button in the vba editor, even though it does what I want the code is still wrong if the macro button does something different? I will make the changes you suggested and see what happens. — James Jordan
@S Meaden, Thank you! It works now. So, it was a small tweak that did it. But one needs the experience to know where to look. Thanks again! — James Jordan

MutjayLee MutjayLee · Accepted Answer · 2016-06-01T11:50:54

0

votes

Did you try changing thisworkbook to workbooks("workbook name")?

Command button and run/sub user form button not in sync

1 Answers