CODEWITHSUNDEEP
vbaexcel
MsAgentM
MsAgentM

Reputation: 153

Data going to wrong workbook

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

Upvotes: 2

Views: 68

Answers (2)

FAB
FAB

Reputation: 2569

@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 :)

Upvotes: 2

Nathan_Sav
Nathan_Sav

Reputation: 8531

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.

Upvotes: 0

Related Questions