Copy And Paste Row If Cell Value Matches A Sheet Name

[Trundlethegr8]

Good morning,

I am tracking customer expenditures. I have a Summary sheet that lists all the Customers and shows the current expenditure data. Each day I copy the data for each Customer to their individual tab and have a historical data set to reference later on. I am trying to set it up to copy rows from my Summary sheet and paste into the sheet name that matches column A. On the destination sheets, id like the existing data to be moved down one row prior to pasting the new data in row 2 so that I have a log of each entry. Row 1 has the column titles.

I am just beginning to use VBA and I currently have it set up to copy row 4 to sheet 1, row 5 to sheet 2, etc. and it definitely has its limits.

I am using Excel 2016 and on a PC.

Any help or advice would be greatly appreciated.

Thanks!

 

[Trundlethegr8]

Also, is there a way to click on a cell in column A of the Summary sheet and jump to the corresponding sheet that matches the cell value?

 

[Trundlethegr8]

Update. I found the following code after doing some more searching. This works perfectly, except I just realized I need to paste values.

Sub IncToCntySheet()

Application.ScreenUpdating = False

Dim rs As Worksheet
Set rs = Worksheets("Summary")

For r = 1 To rs.Range("A" & Rows.Count).End(xlUp).Row
wsName = rs.Cells(r, "A")

If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "False" Then
wr = Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).Row + 1
rs.Rows(r).Copy Destination:=Worksheets(wsName).Range("A" & wr)

End If

Next r

MsgBox "Incident List Copied to Sheet"

Application.ScreenUpdating = True

End Sub

 

[Joe4]

This works perfectly, except I just realized I need to paste values.

Change this part:

rs.Rows(r).Copy Destination:=Worksheets(wsName).Range("A" & wr)

to this:

rs.Rows(r).Copy 
Worksheets(wsName).Range("A" & wr).PasteSpecial Paste:=xlPasteValues

 

[Trundlethegr8]

Thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Trundlethegr8]

How would the code change if I wanted to only copy rows A through O? Sorry for the extra churn.

I'm excited to learn how to do this myself as there are just so many possibilities to make things so much easier.

 

[Joe4]

How would the code change if I wanted to only copy rows A through O? Sorry for the extra churn.

I'm excited to learn how to do this myself as there are just so many possibilities to make things so much easier.

Change this line:

rs.Rows(r).Copy

to this:

rs.Range(Cells(r, "A"), Cells(r, "O")).Copy