Moving data in columns to another sheet VBA

[JoRyBar]

Hello,

I am working on code to move details on one sheet to another. I would like details from Sheet5 to populate Sheet7. For example, I would like Columns B-I to automatically populate columns B-I on Sheet7. I tried working on just one column ($E$7:E7 on sheet 5) to ($E$8:E8 on sheet7) with this code on sheet7:

Sub copyColumns()
Dim dataSheet As Worksheet
Dim target As Worksheet

Dim rowStartD As Integer
Dim rowStartT As Integer
Dim rowEndD As Long


Set dataSheet = ThisWorkbook.Sheets("Sheet5")
Set target = ThisWorkbook.Sheets("Sheet7")

rowStartD = 7
rowStartT = 8
rowEndD = dataSheet.Cells(Rows.Count, "E").End(xlUp).Row

With dataSheet

.Range(.Cells(rowStartD, 7), .Cells(rowEndD, 8)).Copy (target.Cells(rowStartT, 1))

End With
End Sub

No luck . Should this code be on Sheet5? I already have so much coding, I would like to avoid adding more to sheet5 codes. HELP! Your help is greatly appreciated!! Thanks, Gurus!

Novice

 

[Scott T]

If you want the code to run automatically it needs to be based on an event. The below code should be put in Sheet5 and will run when a cell in the range B7:I8 is change. Adjust the range as need.

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B7:I8")) Is Nothing Then
    
    Dim dataSheet As Worksheet
    Dim targetsh As Worksheet
    Dim tcol As Long
    
    Dim rowStartD As Integer
    Dim rowStartT As Integer
    Dim rowEndD As Long
    
    tcol = target.Column
    Set dataSheet = ThisWorkbook.Sheets("Sheet5")
    Set targetsh = ThisWorkbook.Sheets("Sheet7")
    
    rowStartD = 7
    rowStartT = 8
    rowEndD = dataSheet.Cells(Rows.Count, tcol).End(xlUp).Row
    
    targetsh.Cells(target.Row, tcol) = target
    
 
End If
End Sub

 

[JoRyBar]

Hello,

Thanks for replying! So the main issue I am experiencing with the above code is when a new row is added on Sheet 5. I thought about what you mentioned about an action needed for the above code to work. Currently on Sheet 5, I have a command button to add new row and also copy some formulas when the new row is added. Would I be able to amend this code so 1) columns B:I are copied from Sheet 5 to Sheet 7, 2) when a new row is added on Sheet 5 using the command button, it also adds the row in the same location on Sheet 7, and 3) when formulas on Sheet 5 are copied with the new row, formulas are also copied on Sheet 7 (but different columns, also the formulas are different). The current code I have on sheet 5 is:

Private Sub CommandButton1_Click()
Dim rowNum As Long
On Error Resume Next
rowNum = Application.InputBox(Prompt:="Enter Row Number to Add a Row Above:", _
Title:="Add New Row", Type:=1)
Rows(rowNum).Insert Shift:=xlDown
If Err.Number > 0 Then GoTo errH
Range("b" & rowNum - 1).Resize(, 2).Copy Range("b" & rowNum)
Range("c" & rowNum - 1).Resize(, 2).Copy Range("c" & rowNum)
Range("f" & rowNum - 1).Resize(, 2).Copy Range("f" & rowNum)
Range("g" & rowNum - 1).Resize(, 2).Copy Range("g" & rowNum)
Range("h" & rowNum - 1).Resize(, 2).Copy Range("h" & rowNum)


errH:
End Sub

I hope this makes sense. Thanks for your help, Guru!

Novice