Thank you in advance for your help. I have two worksheets, one with census data (Sheet1) and another with account balance data (Table 1). I'm trying to come up with a code that transfers the individuals account balances between the sheets.
I'm able to get the code to match up names with the chart, check which account source ("Profit Sharing" in this example) and to bring the data where I want it. However, the only problem is that it pastes every iteration of every persons balance in each cell where they need to go, ultimately with the last balance pasted uniformly into Sheet1.
For example:
John: pastes Johns, Bills, Dillans, then pastes Jerry's Profit Sharing data in cell cell P1
Bill: pastes Johns, Bills, Dillans, then pastes jerry's Profit Sharing data in cell P2
.
.
Jerry: pastes Johns, Bills, Dills, then pastes Jerry's Profit Sharing data in cell P4.
The thing is, I don't want to change the coding I currently have too much, because I'm setting this up for difference census's with different reports, and would just like to automate doing this for 100's of people across different sheets with the same sheet names. So things need to be dynamic. Again I appreciate any kind of help on this.
I plan extrapolating so it wouldn't be just Profit Sharing, but 401k, Roth 401k, etc.
Here's my current code:
Sub ProfitSharing()
Dim rng As Range
Dim rng2 As Range
Dim i As Integer
Dim finalrow As Integer
finalrow = Sheets("Table 1").Range("A100").End(xlUp).Row
For Each rng In Sheets("Sheet1").Range("A1:A100")
For Each rng2 In Sheets("Table 1").Range("A1:A100")
If rng = rng2 Then
For i = 4 To finalrow
Sheets("Table 1").Range("A" & i).Select
If ActiveCell = "Profit Sharing" Then
ActiveCell.Offset(0, 6).Copy
rng.Offset(0, 15).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next
End If
Next
Next
End Sub
I'm able to get the code to match up names with the chart, check which account source ("Profit Sharing" in this example) and to bring the data where I want it. However, the only problem is that it pastes every iteration of every persons balance in each cell where they need to go, ultimately with the last balance pasted uniformly into Sheet1.
For example:
John: pastes Johns, Bills, Dillans, then pastes Jerry's Profit Sharing data in cell cell P1
Bill: pastes Johns, Bills, Dillans, then pastes jerry's Profit Sharing data in cell P2
.
.
Jerry: pastes Johns, Bills, Dills, then pastes Jerry's Profit Sharing data in cell P4.
The thing is, I don't want to change the coding I currently have too much, because I'm setting this up for difference census's with different reports, and would just like to automate doing this for 100's of people across different sheets with the same sheet names. So things need to be dynamic. Again I appreciate any kind of help on this.
I plan extrapolating so it wouldn't be just Profit Sharing, but 401k, Roth 401k, etc.
Here's my current code:
Sub ProfitSharing()
Dim rng As Range
Dim rng2 As Range
Dim i As Integer
Dim finalrow As Integer
finalrow = Sheets("Table 1").Range("A100").End(xlUp).Row
For Each rng In Sheets("Sheet1").Range("A1:A100")
For Each rng2 In Sheets("Table 1").Range("A1:A100")
If rng = rng2 Then
For i = 4 To finalrow
Sheets("Table 1").Range("A" & i).Select
If ActiveCell = "Profit Sharing" Then
ActiveCell.Offset(0, 6).Copy
rng.Offset(0, 15).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next
End If
Next
Next
End Sub
