Macro moving cells from sheet to sheet

[mericleed]

Hi,

I'm new to using macros in excel, so please forgive me for my lack of knowledge or if I'm doing something completely wrong.

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

 

[kevin9999]

Couple of things to try. Firstly after this line:

lastRow = targetSheet.Cells(targetSheet.Rows.Count, "AJ").End(xlUp).Row

put this:

MsgBox lastRow

and see what the last row is in column AJ

Also, with this line:

targetSheet.Cells(lastRow, 1).PasteSpecial

you're telling the code to copy the values into column 1 (= column A). If you want it to copy to column AJ, it should be this:

targetSheet.Cells(lastRow, 36).PasteSpecial

 

[mericleed]

Thanks Rich! This helped me get to where I could figure it out!
If anyone wants to know, this ended up being the solution.

Sub CopyDataToAnotherSheet()

    Dim sourceSheet As Worksheet
    Dim destSheet As Worksheet
      
    Set sourceSheet = ThisWorkbook.Sheets("CIPs")
    Set targetSheet = ThisWorkbook.Sheets("CIP 2024")
    
    Dim sourceBTR2 As Range, targetBTR2 As Range
    Dim BTR2i As Integer
    
    For BTR2i = 107 To 122
        
        If sourceSheet.Cells(BTR2i, 32).Value > 0 And sourceSheet.Cells(BTR2i, 42).Value > 0 Then
           Set sourceBTR2 = sourceSheet.Range(sourceSheet.Cells(23 + BTR2i - 107, 21), sourceSheet.Cells(23 + BTR2i - 107, 25))
            
           Set targetBTR2 = targetSheet.Cells(targetSheet.Rows.Count, 36).End(xlUp).Offset(1, 0)
            
            If IsEmpty(targetBTR2.Value) Then
                sourceBTR2.Copy
                targetBTR2.PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
            Else
                Set targetBTR2 = targetSheet.Cells(targetSheet.Rows.Count, 36).End(xlUp).Offset(1, 0)
                sourceBTR2.Copy
                targetBTR2.PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
            End If
        Else
            Exit For
        End If
    Next BTR2i
End Sub

 

[SimpleDAS]

Hi, would someone be able to break down for me how this Macro works? (in above solution)

Trying to implement something similar for my own sheet: Need some help with a Macro but I don't know enough about Macros to try and solo this one....