Forum Discussion

mericlee's avatar
mericlee
Copper Contributor
Jan 03, 2024

Help with macro moving cells from sheet to sheet

Hi,

I'm new to using macros in excel, so please forgive me for my lack of knowledge.

I am trying to move cells that auto-populate from specific areas on a source sheet (CIP), to corresponding specific areas on a master log target sheet (CIP 2024). It also needs to find the next empty space in correct area on the target sheet and paste the data there. 

The code I have written sort of works...except instead of moving it to column AJ row 4, in the target sheet, it moves to column A and all the way down to rows in the 900s. 

Any help is greatly appreciated.

 

Here is my code thus far:

Sub CopyDataToAnotherSheet()

    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim BTR2 As Range
    Dim lastRow As Long
    
    Set sourceSheet = ThisWorkbook.Sheets("CIPs")
    Set targetSheet = ThisWorkbook.Sheets("CIP 2024")
    
    Set BTR2 = sourceSheet.Range("U23:Y38")
    
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "AJ").End(xlUp).Row
    
    BTR2.Copy
    targetSheet.Cells(lastRow, 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
End Sub
developer
excel
Formulas and Functions
Macros and VBA
  • djclements's avatar
    djclements
    Bronze Contributor
    Jan 04, 2024

    mericlee Your code looks fine, with the exception of the second to last line...

     

    'Original code:
    targetSheet.Cells(lastRow, 1).PasteSpecial Paste:=xlPasteValues

'Should be "nextRow" (lastRow + 1) and column "AJ":
    targetSheet.Cells(lastRow + 1, "AJ").PasteSpecial Paste:=xlPasteValues

'Or, use the column number (36):
    targetSheet.Cells(lastRow + 1, 36).PasteSpecial Paste:=xlPasteValues

     

    Having said that, I'm wondering why you expected the target range to be column AJ row 4? If the results were output to a row in the 900's, it's because the "lastRow" with data in column AJ was found in the 900's. If that's not what you expected, check column AJ and clear any unwanted data first.

     

    Also, in the interest of "best practices", I would recommend avoiding the Copy, PasteSpecial and CutCopyMode methods by assigning the source values directly to the output range as follows:

     

    Option Explicit

Sub CopyDataToAnotherSheet()

'Load the source data to an array
    Dim wsSource As Worksheet, data As Variant
    Set wsSource = Sheets("CIPs")
    data = wsSource.Range("U23:Y38").Value

'Find the next available row on the output worksheet
    Dim wsOutput As Worksheet, rowId As Long
    Set wsOutput = Sheets("CIP 2024")
    rowId = wsOutput.Cells(wsOutput.Rows.Count, 36).End(xlUp).Row + 1

'Write the array to the output range
    wsOutput.Cells(rowId, 36).Resize(UBound(data, 1), UBound(data, 2)).Value = data

End Sub

Resources