2

I have a workbook coded to collect info from it and transfer the data to a summary workbook. The code works when collecting the data but it prints the data to workbook the data is collected from, not the summary workbook. Its weird because it opens the summary workbook and even counts the row so the data will go to the first empty row. Can someone tell me what I'm doing wrong?

Dim WB1 as workbook
Dim MyData as workbook
Dim Assignment as string
Dim RowCount as integer

Set WB1 = ThisWorkbook
    Assignment = Cells(45, "C")

WB1.Save

Set Mydata= Workbooks.Open (*File path to summary data document*)

MyData.Worksheets("Sheet1").Select
    Worksheets("Sheet1").Range("a1").Select
RowCount = Worksheets("Sheet1").Cells(Rows.Count, "c").End(xlUp).Row + 1

With MyData.Worksheets("Sheet1").Range("A1")
    Cells(RowCount, "b") = Assignment
End With

MyData.Save

End Sub
MsAgentM
  • 153
  • 2
  • 14

2 Answers2

2

@Nathan_Sav notice where the problem is, but not exactly what the problem is..

You are missing one dot from Cells(RowCount, "b"). Without the dot before this, is like you are not using With, referring the ActiveSheet range only.

I believe this should work:

With MyData.Worksheets("Sheet1").Range("A1")
    .Cells(RowCount, "b") = Assignment
End With

Disclaimer: this should resolve which workbook/worksheet your data is added to, not necessarily that is the correct range... but it should give you an idea on what to do next. Hope it helps :)

FAB
  • 2,505
  • 1
  • 10
  • 21
  • That did it! Thanks. I have to wait 1 minute before accepting the answer. – MsAgentM May 22 '18 at 13:49
  • Was writing out my answer, but this gives the gist of it: When you do not specify the workbook that a worksheet belongs to, or not specify the worksheet that a range of cells belongs to the code uses whichever workbook and worksheet is active at the time. Also I think you could remove the `.Range("A1")` from the `With` statement. – Darren Bartrup-Cook May 22 '18 at 13:51
  • To add some extra, please keep in mind that each time you are referring to ranges without specifically declaring them, it will point to activeworkbook/activesheet ones (i.e. `MyData.Worksheets("Sheet1").Select` is correctly referring to your new workbook `Worksheets("Sheet1").Range("a1").Select` this however selects the same workbook, because is the active one... and you can combine this in one line `MyData.Worksheets("Sheet1").Range("a1").Select`. Further advise, you can avoid that select all together by `RowCount = MyData.Worksheets("Sheet1").Cells(Rows.Count, "c").End(xlUp).Row + 1` – FAB May 22 '18 at 13:56
  • This is probably the most common error in [tag:VBA]. Question from 3 hours ago about the same - https://stackoverflow.com/questions/50465500/variable-not-being-stored-in-the-code-when-sheet-changes/ – Vityata May 22 '18 at 14:03
0

Cells(RowCount, "b") = Assignment needs to be like

With MyData.Worksheets("Sheet1")
    .range("b1").resize(RowCount, 1)= Assignment
End With

Not sure on the resize this pasting region.

Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
  • What do you mean "Not sure on the resize pasting region"? The data won't go to "b1", it will go to whatever the row count in column b. I don't need anything resized. I just need whatever is assigned to the assignment variable to be printed onto cells(rowcount."b") – MsAgentM May 22 '18 at 13:47
  • I didn't know if you were copying a column or not. Or just placing at the end of a columns. – Nathan_Sav May 22 '18 at 15:08