Copy to by cell contents

[plwandatw]

Hello,

I use the following to copy data on change to another worksheet, on the current worksheet D is a category that I need to copy cell E to sheet1 where the categorys broken down into columns D:L

Thank you

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Cat
End If
End Sub

Sub Cat()
Dim irow As Long

irow = ActiveCell.Row

Range(Cells(irow, "A"), Cells(irow, "E")).Copy

Sheets("Sheet1").Select
Range("A" & Rows.Count).End(xlUp).EntireRow.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

End Sub

 

[Joe4]

I think you are going to need to show us some images of your data source and where you are poasting it, as all this is highly dependent upon your sheet structure. So we need to see what it all looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[plwandatw]

ActiveWorkSheet

Check #	Date	To	Expenses Category	Withdrawal		Deposit	Cleared	Balance
								$106,901.94
2706	15-Jul-23	KTQM	Advertising	$237.44				$106,664.50

Sheet1

Check#	Date	Payee	Rodeo	Advertising	CTR	Events	Labor	Donations	OP Expenses
2706	15-Jul-23	KTQM		$ 237.44

 

[Joe4]

Are "Rodeo", "Advertising", "CTR", "Events", "Labor", "Donations", and "OP Expenses" the only possibilities for the Expenses Category?
Will those expenses ALWAYS be listed in that exact order in Sheet1, going across the columns?
Are we only concerned with Withdrawal amounts and not Deposit amounts?

I also see that you have this code being triggered when a value is entered in column D (Expenses Category).
Will this ALWAYS be the last column of data entered?
If that value is entered before an amount is entered in the Withdrawal field, it would be sent over to Sheet1 before there is an amount.

 

[plwandatw]

Are "Rodeo", "Advertising", "CTR", "Events", "Labor", "Donations", and "OP Expenses" the only possibilities for the Expenses Category?
Will those expenses ALWAYS be listed in that exact order in Sheet1, going across the columns?
Are we only concerned with Withdrawal amounts and not Deposit amounts?

I also see that you have this code being triggered when a value is entered in column D (Expenses Category).
Will this ALWAYS be the last column of data entered?
If that value is entered before an amount is entered in the Withdrawal field, it would be sent over to Sheet1 before there is an amount.

Joe4,

Yes, these are the only categories that we are required to provide. Yes, Expenses Category will be the last input (I'm going move this column to E for better flow). Sheet1 categories will not change columns.

Thank you

 

[plwandatw]

I updated the following code that might be a bit cumbersome but it works. The issue now is it only works with worksheet "Jul" is there a way to use the same code for all 12 worksheets (Jan-Dec)?

Sub Cat()
Dim irow As Long

irow = ActiveCell.Row

Application.ScreenUpdating = False
Range(Cells(irow, "A"), Cells(irow, "C")).Copy
Sheets("Sheet1").Select
Range("A" & Rows.Count).End(xlUp).EntireRow.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    True, Transpose:=False
    
    Sheets("jul").Select
    
If Cells(irow, "e").Value = "Rodeo" Then
        Cells(irow, "d").Copy
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 3).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    True, Transpose:=False
      
    ElseIf Cells(irow, "e").Value = "Advertising" Then
        Cells(irow, "d").Copy
        Sheets("Sheet1").Select
        ActiveCell.Offset(0, 4).Select.Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False

    ElseIf Cells(irow, "e").Value = "CTR" Then
        Cells(irow, "d").Copy
        Sheets("Sheet1").Select
        ActiveCell.Offset(0, 5).Select.Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
 
    ElseIf Cells(irow, "e").Value = "Events" Then
        Cells(irow, "d").Copy
        Sheets("Sheet1").Select
        ActiveCell.Offset(0, 6).Select.Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False

    ElseIf Cells(irow, "e").Value = "Labor" Then
        Cells(irow, "d").Copy
        Sheets("Sheet1").Select
        ActiveCell.Offset(0, 7).Select.Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
     
    ElseIf Cells(irow, "e").Value = "Donations" Then
        Cells(irow, "d").Copy
        Sheets("Sheet1").Select
        ActiveCell.Offset(0, 8).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    
    ElseIf Cells(irow, "e").Value = "OP Expenses" Then
        Cells(irow, "d").Copy
        Sheets("Sheet1").Select
        ActiveCell.Offset(0, 9).Select.Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    
    
End If
Sheets("jul").Select
Application.ScreenUpdating = True
End Sub