VBA Help - Copy/Paste 3 Blocks of Data using LastRow - MS Excel 2016

[Johnny Thunder]

Hello,

I am working on a project that requires 3 blocks of data (1 Column) one after the other.

I thought I could use the LastRow Syntax to get this done but it seems to not be updating after I paste the first block on my spreadhseet.

My code so far with comments is below. Any help is appreciated

Sub CopyBlocks()


Dim LastR       As Long, LastRC As Long, lastR2 As Long, LastR3 As Long, lastR4 As Long
Dim Sht         As Worksheet, sht2 As Worksheet, sht3 As Worksheet, sht4 As Worksheet
Dim myloop


Set Sht = Sheet5 'Recon
Set sht2 = Sheet1 'BPC
Set sht3 = Sheet3 'BW
Set sht4 = Sheet4 'Journals


LastR = Sht.Cells(Rows.Count, "B").End(xlUp).Row 'sheet where the blocks need to go
lastR2 = sht2.Cells(Rows.Count, "B").End(xlUp).Row 'Block 1 Data
LastR3 = sht3.Cells(Rows.Count, "C").End(xlUp).Row 'Block 2 Data
lastR4 = sht4.Cells(Rows.Count, "C").End(xlUp).Row 'Block 3 Data
LastRC = Sht.Cells(Rows.Count, "A").End(xlUp).Row 'Redim's the last row after each paste - Currently not working


Application.ScreenUpdating = False
    
sht2.Range("A12:A" & lastR2 & "").Copy 'Block #1 
        Sht.Range("A11").PasteSpecial xlValues 'Start of 1st paste location
            Application.CutCopyMode = False
    
    MsgBox LastRC 'To see if the lastrow updated
    
    sht3.Range("B19:B" & LastR3 & "").Copy 'Block #2 
        Sht.Range("A" & LastRC + 1 & "").PasteSpecial xlValues 'Finds the new LastRow in Col A
            Application.CutCopyMode = False
    
    MsgBox LastRC 'To see if the lastrow updated
    
    sht4.Range("A14:A" & lastR4 & "").Copy 'Block #3 
        Sht.Range("A" & LastRC + 1 & "").PasteSpecial xlValues 'Finds the new LastRow in Col A
            Application.CutCopyMode = False
    
    Calculate
    
    Application.ScreenUpdating = True



End Sub

 

[Joe4]

The problem is this does NOT get re-calculated unless you redo after each paste:

LastRC = Sht.Cells(Rows.Count, "A").End(xlUp).Row 'Redim's the last row after each paste - Currently not working

LastRC is calculated at that point in time, and is not automatically calculated, as it is just a hard-coded number.
Hard-coded numbers do not get automatically recalculated.

You would need to do the calculation again after each paste, to reflect the new position.

 

[Johnny Thunder]

Ok, finally got it to work. Made a small change on how the LastRow was being found. My First Method was starting from the Top and looking down which resulted in not getting the most current LastRow, the update is looking from the lastRow in the workbook ("A1048576") and moving up to find the LastRow.


Working

Sub CopyBlocks()

Dim LastR       As Long, LastRC As Long, lastR2 As Long, LastR3 As Long, lastR4 As Long
Dim Sht         As Worksheet, sht2 As Worksheet, sht3 As Worksheet, sht4 As Worksheet
Dim myloop


Set Sht = Sheet5 'Recon
Set sht2 = Sheet1 'BPC
Set sht3 = Sheet3 'BW
Set sht4 = Sheet4 'Journals


LastR = Sht.Cells(Rows.Count, "B").End(xlUp).Row
lastR2 = sht2.Cells(Rows.Count, "B").End(xlUp).Row
LastR3 = sht3.Cells(Rows.Count, "C").End(xlUp).Row
lastR4 = sht4.Cells(Rows.Count, "C").End(xlUp).Row
LastRC = Sht.Range("A1048576").End(xlUp).Row


Application.ScreenUpdating = False


Sht.Range("A11:A" & LastRC & "").ClearContents
    
    sht2.Range("A12:A" & lastR2 & "").Copy 'Block #1 
        Sht.Range("A11").PasteSpecial xlValues 'Start of 1st paste location
            Application.CutCopyMode = False
        
    sht3.Range("B19:B" & LastR3 & "").Copy 'Block #2 
        Sht.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlValues 'Finds the new LastRow in Col A
            Application.CutCopyMode = False
       
    sht4.Range("A14:A" & lastR4 & "").Copy 'Block #3 
        Sht.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlValues 'Finds the new LastRow in Col A
            Application.CutCopyMode = False
            
    
    Calculate
    
    Application.ScreenUpdating = True


End Sub

 

[Joe4]

Note that:

Sht.Cells(Rows.Count, "A").End(xlUp).Row

and

Sht.Range("A1048576").End(xlUp).Row

do the exact same thing. They both find the last populated row in column A.
So making that change did nothing.

What fixed it was changing the references like this:

Sht.Range("A" & LastRC + 1 & "")

which uses the last row calculation from the beginning of your code (and never changes), to this:

Sht.Range("A1048576").End(xlUp).Offset(1, 0)

which recalculates the last row at that point in time when that row of code is being run.