Vba Range.Value = Range.Value not working

[jondavis1987]

Here is some of the data I am working with. The sieves portion works perfectly. However, none of the samples data is changing in the destination wb. I'm not exactly sure what i'm doing wrong. Any suggestions? None of the cells are merged. Some of the cells that i'm trying to change to are the exact same cells in the sieves portion, but none of the samples portion is changing.

'   Copy Sieve data from source workbook to destination workbook
   
    destWB.Sheets("Sieves").Range("A" & destWB.Sheets("Sieves").Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(12, 1).Value = srcWB.Sheets("A").Range("G20").Value
    destWB.Sheets("Sieves").Range("B" & destWB.Sheets("Sieves").Cells(Rows.Count, "B").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("H20").Value
    destWB.Sheets("Sieves").Range("C" & destWB.Sheets("Sieves").Cells(Rows.Count, "C").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("G3").Value
    destWB.Sheets("Sieves").Range("D" & destWB.Sheets("Sieves").Cells(Rows.Count, "D").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("G5").Value
    destWB.Sheets("Sieves").Range("E" & destWB.Sheets("Sieves").Cells(Rows.Count, "E").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("B6").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 11).Value = srcWB.Sheets("A").Range("A10").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 10).Value = srcWB.Sheets("A").Range("A11").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 9).Value = srcWB.Sheets("A").Range("A12").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 8).Value = srcWB.Sheets("A").Range("A13").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 7).Value = srcWB.Sheets("A").Range("A14").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 6).Value = srcWB.Sheets("A").Range("A15").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 5).Value = srcWB.Sheets("A").Range("A16").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 4).Value = srcWB.Sheets("A").Range("A17").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 3).Value = srcWB.Sheets("A").Range("A18").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 2).Value = srcWB.Sheets("A").Range("A19").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row - 1).Value = srcWB.Sheets("A").Range("A20").Value
    destWB.Sheets("Sieves").Range("F" & destWB.Sheets("Sieves").Cells(Rows.Count, "F").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("A21").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 11).Value = srcWB.Sheets("A").Range("D10").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 10).Value = srcWB.Sheets("A").Range("D11").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 9).Value = srcWB.Sheets("A").Range("D12").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 8).Value = srcWB.Sheets("A").Range("D13").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 7).Value = srcWB.Sheets("A").Range("D14").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 6).Value = srcWB.Sheets("A").Range("D15").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 5).Value = srcWB.Sheets("A").Range("D16").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 4).Value = srcWB.Sheets("A").Range("D17").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 3).Value = srcWB.Sheets("A").Range("D18").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 2).Value = srcWB.Sheets("A").Range("D19").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 1).Value = srcWB.Sheets("A").Range("D20").Value
    destWB.Sheets("Sieves").Range("G" & destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row - 0).Value = srcWB.Sheets("A").Range("D21").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 11).Value = srcWB.Sheets("A").Range("G21").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 10).Value = srcWB.Sheets("A").Range("G22").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 9).Value = srcWB.Sheets("A").Range("G23").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 8).Value = srcWB.Sheets("A").Range("G24").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 7).Value = srcWB.Sheets("A").Range("G25").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 6).Value = srcWB.Sheets("A").Range("G26").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 5).Value = srcWB.Sheets("A").Range("G27").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 4).Value = srcWB.Sheets("A").Range("G28").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 3).Value = srcWB.Sheets("A").Range("G29").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 2).Value = srcWB.Sheets("A").Range("G30").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row - 1).Value = srcWB.Sheets("A").Range("G31").Value
    destWB.Sheets("Sieves").Range("H" & destWB.Sheets("Sieves").Cells(Rows.Count, "H").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("G32").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 11).Value = srcWB.Sheets("A").Range("H21").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 10).Value = srcWB.Sheets("A").Range("H22").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 9).Value = srcWB.Sheets("A").Range("H23").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 8).Value = srcWB.Sheets("A").Range("H24").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 7).Value = srcWB.Sheets("A").Range("H25").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 6).Value = srcWB.Sheets("A").Range("H26").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 5).Value = srcWB.Sheets("A").Range("H27").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 4).Value = srcWB.Sheets("A").Range("H28").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 3).Value = srcWB.Sheets("A").Range("H29").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 2).Value = srcWB.Sheets("A").Range("H30").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row - 1).Value = srcWB.Sheets("A").Range("H31").Value
    destWB.Sheets("Sieves").Range("I" & destWB.Sheets("Sieves").Cells(Rows.Count, "I").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("H32").Value
    destWB.Sheets("Sieves").Range("J" & destWB.Sheets("Sieves").Cells(Rows.Count, "J").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("D22").Value
    destWB.Sheets("Sieves").Range("K" & destWB.Sheets("Sieves").Cells(Rows.Count, "K").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("G47").Value
    destWB.Sheets("Sieves").Range("L" & destWB.Sheets("Sieves").Cells(Rows.Count, "L").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("H47").Value
    destWB.Sheets("Sieves").Range("M" & destWB.Sheets("Sieves").Cells(Rows.Count, "M").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("C53").Value
    destWB.Sheets("Sieves").Range("N" & destWB.Sheets("Sieves").Cells(Rows.Count, "N").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("G48").Value
    destWB.Sheets("Sieves").Range("O" & destWB.Sheets("Sieves").Cells(Rows.Count, "O").End(xlUp).Row - 11).Resize(12, 1).Value = srcWB.Sheets("A").Range("H48").Value


    
'   Copy Samples data from source workbook to destination workbook
    destWB.Sheets("Samples").Range("A" & destWB.Sheets("Samples").Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = srcWB.Sheets("A").Range("G20").Value
    destWB.Sheets("Samples").Range("B" & destWB.Sheets("Samples").Cells(Rows.Count, "B").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("H20").Value
    destWB.Sheets("Samples").Range("C" & destWB.Sheets("Samples").Cells(Rows.Count, "C").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("G3").Value
    destWB.Sheets("Samples").Range("D" & destWB.Sheets("Samples").Cells(Rows.Count, "D").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("G5").Value
    destWB.Sheets("Samples").Range("E" & destWB.Sheets("Samples").Cells(Rows.Count, "E").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("B6").Value
    destWB.Sheets("Samples").Range("F" & destWB.Sheets("Samples").Cells(Rows.Count, "F").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("D31").Value
    destWB.Sheets("Samples").Range("G" & destWB.Sheets("Samples").Cells(Rows.Count, "G").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("E31").Value
    destWB.Sheets("Samples").Range("H" & destWB.Sheets("Samples").Cells(Rows.Count, "H").End(xlUp).Row + 0).Value = srcWB.Sheets("A").Range("F31").Value

 

[Fluff]

Are you deliberately looking to overwrite the existing data in columns B:H?

 

[jondavis1987]

Sorry, didn’t clarify that part. Both sieves and samples are giant tables. So the way sieves worked is the first entry in A causes the table to expand so now the end of the table is the last row. So in samples B:H isn’t overwriting previous data. Should be overwriting blank cells in a table.

 

[Fluff]

In that case there should be no problem, are you sure that the cells on A are not blank?

 

[Fluff]

You can also slim that code down like

   Set SrcWs = SrcWs
   With destwb.Sheets("sieves")
      .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(12, 1).Value = SrcWs.Range("G20").Value
      .Range("B" & .Cells(Rows.Count, "B").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("H20").Value
      .Range("C" & .Cells(Rows.Count, "C").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("G3").Value
      .Range("D" & .Cells(Rows.Count, "D").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("G5").Value
      .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("B6").Value
      .Range("F" & .Cells(Rows.Count, "F").End(xlUp).Row - 11).Resize(12).Value = SrcWs.Range("A10:A21").Value
      .Range("G" & .Cells(Rows.Count, "G").End(xlUp).Row - 11).Resize(12).Value = SrcWs.Range("D10:D21").Value
      .Range("H" & .Cells(Rows.Count, "H").End(xlUp).Row - 11).Resize(12).Value = SrcWs.Range("G21:G32").Value
      .Range("I" & .Cells(Rows.Count, "I").End(xlUp).Row - 11).Resize(12).Value = SrcWs.Range("H21:H32").Value
      .Range("J" & .Cells(Rows.Count, "J").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("D22").Value
      .Range("K" & .Cells(Rows.Count, "K").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("G47").Value
      .Range("L" & .Cells(Rows.Count, "L").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("H47").Value
      .Range("M" & .Cells(Rows.Count, "M").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("C53").Value
      .Range("N" & .Cells(Rows.Count, "N").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("G48").Value
      .Range("O" & .Cells(Rows.Count, "O").End(xlUp).Row - 11).Resize(12, 1).Value = SrcWs.Range("H48").Value
   End With

 

[jondavis1987]

A should be pasting in the empty row under the table to expand the table and it should be a month. The same month that’s getting put in column a of sieves. It’s honestly baffling me why nothing is changing in samples. I went with the value equals value method instead of copy and paste because with copy and paste in the samples portion it will give me an error the second time running it saying can’t paste because the paste area is different. So doing both methods seems to give me a problem in some way right there.

 

[Fluff]

Goto A1048576 on the samples sheet & hit Ctrl & up arrow, does it take you to the bottom of the table?

 

[jondavis1987]

I’m gonna have to get back to you tomorrow on that. Power outages kept happening from the bad weather so I headed home early.

 

[Fluff]

Ok. That's fine.

 

[jondavis1987]

Well, now I realize how often i over complicate what's going on. For some reason some things had pasted about 18,000 rows below where they should have. So now after having deleted them the ctl + up takes me to the bottom of the table. Using an old laptop at home. It's painfully slow so I didn't download all of the workbooks from dropbox. Just the one to let me see if it had information in it. Thank you for the help!!!