If call value in master sheet equals Tab name, copy rows with that name value into Tab

[mbkinzer]

Hi guys!

I currently have a workbook that has a master tab (ACCTHX) and a tab created for every member. The ACCTHX tab has a line for each member and each month (so one member will have 12 lines- sometimes more). I need to copy and paste their 12 or more rows (columns D:H) into their corresponding tab into a specific cell (G4). Any help would be appreciated!

This is what I currently have but is bombing out:

Dim wkSht As Worksheet
Dim numrow As Long
Range("A1").Select
numrow = Range(Selection, Selection.End(xlDown)).Count
Range("A1").Value = numrow
For Each wkSht In Sheets
If ActiveSheet.Range(numrow).Value = wkSht.Name Then
ActiveSheet.Range("D:H").CurrentRegion.Copy Destination:=wkSht.Range("G4")

 

[Zot]

It is not quite clear your description for me

You have a workbook
The workbook has a worksheet named ACCTHX
The workbook also has many other worksheet, one for every member
The sheet ACCTHX has a line for each member (a column for each member? Column D:H, meaning 5 members?)
Each member column has 12 or more rows
You need to copy 12 or more rows to corresponding tab (so the tab is named by member name?) into specific cell G4.

The member name is in Column D to H? What row?
Meaning you want to copy data from column (for example) D2 to D12, assuming D1 is member name, into respective member sheet (as named by tab)

Please re-clarify

 

[mbkinzer]

Hi guys!

I currently have a workbook that has a master tab (ACCTHX) and a tab created for every member. The ACCTHX tab has a line for each member and each month (so one member will have 12 lines- sometimes more). I need to copy and paste their 12 or more rows (columns D:H) into their corresponding tab into a specific cell (G4). Any help would be appreciated!

This is what I currently have but is bombing out:

Dim wkSht As Worksheet
Dim numrow As Long
Range("A1").Select
numrow = Range(Selection, Selection.End(xlDown)).Count
Range("A1").Value = numrow
For Each wkSht In Sheets
If ActiveSheet.Range(numrow).Value = wkSht.Name Then
ActiveSheet.Range("D:H").CurrentRegion.Copy Destination:=wkSht.Range("G4")

It is not quite clear your description for me

You have a workbook
The workbook has a worksheet named ACCTHX
The workbook also has many other worksheet, one for every member
The sheet ACCTHX has a line for each member (a column for each member? Column D:H, meaning 5 members?)
Each member column has 12 or more rows
You need to copy 12 or more rows to corresponding tab (so the tab is named by member name?) into specific cell G4.

The member name is in Column D to H? What row?
Meaning you want to copy data from column (for example) D2 to D12, assuming D1 is member name, into respective member sheet (as named by tab)

Please re-clarify

Hi!
The ACCTHx master sheet has a line for every member and they may have several lines. The columns have their acct hx info are D:H and I need columns D:H to be copied into the member's tab based on their name match (Column A). So if the member's name is found in column A, take info from column D:H of all lines with that member's name and paste into their respective tab that is already named after them. The members are in alphabetical order on the ACCTHX tab, so their lines are clumped together.

 

[Zot]

Can you use XL2BB to copy paste the master sheet? This way every one can copy data easily to work and test the solution.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

or you can just upload to cloud a sample that people can download and work on it?

 

[mbkinzer]

Can you use XL2BB to copy paste the master sheet? This way every one can copy data easily to work and test the solution.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

or you can just upload to cloud a sample that people can download and work on it?

Here is a copy of an example of what the Master sheet looks like. So everyone in column A has one tab for each individual name (here there would be 3 tabs- Jane Doe, John Smith, and James Johnson.) I need for the macro to look at the names in column A, and copy that member's columns D-H (12 rows, but could be more) into each member's tab into cell G4 of that tab. So for example for Jane Doe, starting at D1 to H12 copy and paste into her tab. For John Smith, copy D13 to H24 and so on for each member.

A	B	C	D	E	F	G	H
JANE DOE	1999​	1​	July		100​		5​
JANE DOE	1999​	2​	August		100​		10​
JANE DOE	1999​	3​	September		200​		11​
JANE DOE	1999​	4​	October		300​		15​
JANE DOE	1999​	5​	November		100​		15​
JANE DOE	1999​	6​	December		500​		15​
JANE DOE	1999​	7​	January		400​		16​
JANE DOE	1999​	8​	February		300​		12​
JANE DOE	1999​	9​	March		700​		13​
JANE DOE	1999​	10​	April		700​		17​
JANE DOE	1999​	11​	May		200​		20​
JANE DOE	1999​	12​	June		300​		20​
JOHN SMITH	2000​	1​	July		100​		5​
JOHN SMITH	2000​	2​	August		100​		10​
JOHN SMITH	2000​	3​	September		200​		11​
JOHN SMITH	2000​	4​	October		300​		15​
JOHN SMITH	2000​	5​	November		100​		15​
JOHN SMITH	2000​	6​	December		600​		15​
JOHN SMITH	2000​	7​	January		800​		16​
JOHN SMITH	2000​	8​	February		800​		12​
JOHN SMITH	2000​	9​	March		800​		13​
JOHN SMITH	2000​	10​	April		700​		8​
JOHN SMITH	2000​	11​	May		200​		9​
JOHN SMITH	2000​	12​	June		300​		10​
JAMES JOHNSON	1998​	1​	July		200​		16​
JAMES JOHNSON	1998​	2​	August		300​		12​
JAMES JOHNSON	1998​	3​	September		100​		13​
JAMES JOHNSON	1998​	4​	October		600​		8​
JAMES JOHNSON	1998​	5​	November		800​		9​
JAMES JOHNSON	1998​	6​	December		800​		10​
JAMES JOHNSON	1998​	7​	January		800​		9​
JAMES JOHNSON	1998​	8​	February		700​		12​
JAMES JOHNSON	1998​	9​	March		300​		13​
JAMES JOHNSON	1998​	10​	April		300​		15​
JAMES JOHNSON	1998​	11​	May		300​		6​
JAMES JOHNSON	1998​	12​	June		300​		17​

 

[Zot]

I have one more question. Example in 1st row for Jane Doe, you have D2(July), F2(100 and), H2(5) to copy to G4 of Jane Doe tab (or sheet). How do you want it copied? Multiple data into one cell? What about the rest 11 rows of Jane Doe data?

 

[Zot]

Ohh... Probably you meant from D2:H2 to range G4:K4 and so on?

 

[Zot]

I cannot fully understand your requirement but not to waste time waiting for answer since we are on different time zone (I think), here is a sample of code. It can be easily modified to your need. I put remark so that you can easily follow and modify.

Sub Copy2Tab()

Dim NameX$
Dim eRow&, nRow&
Dim cell As Range, rngName As Range
Dim wsACCTHX As Worksheet, wsX As Worksheet

Set wsACCTHX = ActiveWorkbook.Sheets("ACCTHX")

Application.ScreenUpdating = False

' Find Last row of name list and define range
eRow = wsACCTHX.Range("A1").End(xlDown).Row
Set rngName = wsACCTHX.Range("A1", "A" & eRow)

For Each cell In rngName
    NameX = cell.Value2
    ' Add sheet with name if not yet exist at the most right sheet
    If Not GotTab(NameX) Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameX
    End If
    Set wsX = ActiveWorkbook.Sheets(NameX)
    ' Find last row with item in target sheet
    nRow = wsX.Range("A" & wsX.Cells.Rows.Count).End(xlUp).Row
    If Not Len(wsX.Range("A" & nRow)) = 0 Then
        nRow = nRow + 1
    End If
    ' Write data to sheet
    wsACCTHX.Range("D" & cell.Row, "H" & cell.Row).Copy
    wsX.Range("A" & nRow).PasteSpecial (xlPasteValues)                ' Here it is writing to column A in designated sheet
Next

End Sub

Function GotTab(strName As String) As Boolean

Dim ws As Worksheet

GotTab = False
For Each ws In ActiveWorkbook.Sheets
    If ws.Name = strName Then
        GotTab = True
    End If
Next

End Function

 

[mbkinzer]

I have one more question. Example in 1st row for Jane Doe, you have D2(July), F2(100 and), H2(5) to copy to G4 of Jane Doe tab (or sheet). How do you want it copied? Multiple data into one cell? What about the rest 11 rows of Jane Doe data?

Even though there is nothing in columns E or G, the range of D through H needs to be copied because of the layout of the member’s tab it is being pasted into. So range D:H will go into G4 on member’s tab because that is where it should start. All 12 rows or however many exist for that member’s name need to be copied into G4.

 

[Zot]

Will this work? Copy to G4 and so on

Sub Copy2Tab()

Dim NameX$
Dim eRow&, nRow&
Dim cell As Range, rngName As Range
Dim wsACCTHX As Worksheet, wsX As Worksheet

Set wsACCTHX = ActiveWorkbook.Sheets("ACCTHX")

Application.ScreenUpdating = False

' Find Last row of name list and define range
eRow = wsACCTHX.Range("A1").End(xlDown).Row
Set rngName = wsACCTHX.Range("A1", "A" & eRow)

For Each cell In rngName
    NameX = cell.Value2
    ' Add sheet with name if not yet exist at the most right sheet
    If Not GotTab(NameX) Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameX
    End If
    Set wsX = ActiveWorkbook.Sheets(NameX)
    ' Find last row with item in target sheet
    nRow = 4
    If Not Len(wsX.Range("G" & nRow)) = 0 Then
        nRow = wsX.Range("G" & wsX.Cells.Rows.Count).End(xlUp).Row
        nRow = nRow + 1
    End If
    ' Write data to sheet
    wsACCTHX.Range("D" & cell.Row, "H" & cell.Row).Copy
    wsX.Range("G" & nRow).PasteSpecial (xlPasteValues)                ' Here it is writing to colmun A in designated sheet
Next

End Sub

Function GotTab(strName As String) As Boolean

Dim ws As Worksheet

GotTab = False
For Each ws In ActiveWorkbook.Sheets
    If ws.Name = strName Then
        GotTab = True
    End If
Next

End Function