Working with two worksheets

[LosingMyMindAgain]

Hello. I am fairly new to Excel macros and apologize ahead of time if this is a stupid question. I am looking to work with two sheets of a workbook. I found a macro that does what I want, but only when dealing with one sheet. Sheet1 is the data entry portion and Sheet2 is where the totals will be. With this macro I am hoping to cycle through Sheet1 Column N to distinguish the unique counties listed and then count the occurrences of each county. I'd then like to paste the unique counties on Sheet2 starting at E3. Then also paste the counts on Sheet2 starting at F3. How do I go about tweaking this so I can reference back and forth between the two sheets? Any tips would be greatly appreciated. Thank you!

Sub Special_Countif()
Dim i, LastRowA, LastRowB
'A=Data Column B=Unique Names C=Count
LastRowA = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.EnableEvents = False
Columns("B:C").ClearContents
For i = 1 To LastRowA
If Application.CountIf(Range("B:B"), Cells(i, "A")) = 0 Then
Cells(i, "B").Offset(1, 0).Value = Cells(i, "A").Value
End If
Next
Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRowB
Cells(i, "C").Value = Application.CountIf(Range("A:A"), Cells(i, "B"))
Next i
Range("B1").Value = "Entry"
Range("C1").Value = "Occurrences"
Range("B1:C1").HorizontalAlignment = xlCenter
Range("B1").Select
Columns("B:C").AutoFit
Application.EnableEvents = True
End Sub

 

[hiker95]

LosingMyMindAgain,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs (both will display what cells, rows, columns, your data is in):
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[LosingMyMindAgain]

Thank you Hiker! I am using Excel 2010 on Windows 7. I created a basic Excel of what I am trying to do as mine contains a lot of confidential data. Basically, I would like to automatically take Sheet1 Column C and go through it to identify the unique values. I would then like to paste those unique values on Sheet 2 Column B. I'd also like to count the under of times each unique value appeared in Sheet1 Column C. I'd then like to put those counts on Sheet2 Column C. I'm not worried about the blanks because I can use a simple Excel function to pull that #.

Link: https://app.box.com/s/ynzw9bych6pyq9w18qem

 

[hiker95]

LosingMyMindAgain,

Thanks for the workbook.

I created a basic Excel of what I am trying to do as mine contains a lot of confidential data.

I am confused.

Your descriptions of where your raw data actually is does not match the workbook/worksheets?????


So that I can get it right on the first try, can you post another workbook with:

1. the two correct worksheet names

2. With the titles in the correct row, and, columns, for just the next 4 titles, First, Last, County, and, State - leave the rest of the titles blank.


3. With the titles in the correct row, and, columns, for just the next 3 titles, # People Served,
Counties Served, and, # of Times - leave the rest of the titles blank.

 

[LosingMyMindAgain]

Sorry for the confusion Hiker. I have reloaded the file. I usually just leave the worksheets as Sheet1 and Sheet2. The workbook is typically named MonthlyFundReport. The staff enters in basic data into sheet 1. What I am hoping is for a macro that will review Sheet1 column C. I wanted the macro to go through Sheet1 column C to identify each unique county. The list of unique counties would then be pasted onto Sheet2 column B starting at B2. Then I wanted the macro to count the number of times each unique county appeared in Sheet1 column 2. The totals would then be placed on Sheet2 column C starting at C2. Thank you for looking at this! In my head I know what I am looking for, but I can't see to put it into code.

https://app.box.com/s/p66hfhh71zo09s3vm9ul

 

[hiker95]

LosingMyMindAgain,

Sample worksheets:

Excel 2007
	A	B	C	D
1	First	Last	County	State
2	Joe	Jones	Butler	PA
3	Bob	Smith	Beaver	PA
4	Tom	White	Allegheny	PA
5	Ed	Brown	Westmoreland	PA
6	Rich	Green		OH
7	Steve	Yellow		DE
8	Lou	Red	Allegheny	PA
9	Mary	Purple	Beaver	PA
10	Sally	Green	Allegheny	PA
11	Jo	Snow	Westmoreland	PA
12
Sheet1

Excel 2007
	A	B	C
1
2
3
4
5
6
7
Sheet2

After the macro in worksheet Sheet2:

Excel 2007
	A	B	C
1	# People Served	Counties Served	# of Times
2	10	Allegheny	3
3		Beaver	2
4		Butler	1
5		Westmoreland	2
6		Not Recorded	2
7
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetUniqueCounts()
' hiker95, 09/22/2014, ME807082
Dim w1 As Worksheet, w2 As Worksheet
Dim lr1 As Long, lr2 As Long, n As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
w2.UsedRange.ClearContents
With w1
  lr1 = .Cells(Rows.Count, 4).End(xlUp).Row
  n = Application.CountIf(.Range("C2:C" & lr1), "")
  .Columns(3).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=w2.Columns(2), Unique:=True
End With
With w2
  lr2 = .Cells(Rows.Count, 2).End(xlUp).Row
  .Range("B2:B" & lr2).Sort key1:=.Range("B2"), order1:=1
  With .Cells(1, 1).Resize(, 3)
    .Value = Array("# People Served", "Counties Served", "# of Times")
    .Font.Bold = True
  End With
  .Cells(2, 1).Value = lr1 - 1
  With .Range("C2:C" & lr2)
    .Formula = "=COUNTIF(Sheet1!$C$2:$C$" & lr1 & ",B2)"
    .Value = .Value
  End With
  If n > 0 Then
    .Cells(lr2 + 1, 2).Value = "Not Recorded"
    .Cells(lr2 + 1, 3).Value = n
  End If
  .Columns(1).Resize(, 3).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniqueCounts macro.

 

[LosingMyMindAgain]

Thanks Hiker! I do have a few questions. There will be other data on the sheet2 that won't be controlled by the macro. So I removed w2.UsedRange.ClearContents. Is there a way to start the pasting of the data on the third cell of the column on sheet 2 and to not include the headings. I was confused by the .Value = Array("# People Served", "Counties Served", "# of Times") and broke the macro a couple times trying to remove that lol. The other question was the staff format cells different colors and such. Is there any way to remove that when pasting the data. I did not think of that aspect until I tried it on a spreadsheet we use. Thanks again for all your help!

 

[hiker95]

LosingMyMindAgain,

Thanks Hiker!

You are very welcome. Glad I could help.

There will be other data on the sheet2 that won't be controlled by the macro. So I removed w2.UsedRange.ClearContents. Is there a way to start the pasting of the data on the third cell of the column on sheet 2 and to not include the headings. I was confused by the .Value = Array("# People Served", "Counties Served", "# of Times") and broke the macro a couple times trying to remove that lol. The other question was the staff format cells different colors and such. Is there any way to remove that when pasting the data. I did not think of that aspect until I tried it on a spreadsheet we use. Thanks again for all your help!

I had based my macro on the data structure of the two workbooks/worksheets that you posted links to.

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

In order to continue please post another workbook with Sheet1, and, Sheet2, with their actual raw data, and, results (manually formatted by your) for the results you are looking for.

 

[LosingMyMindAgain]

My apologies Hiker. I thought I would be able to manipulate the macro to fit the workbook I was working on. Lesson learned. I have a lot left to learn with visual basic and its use with Excel. I will edit out all the confidential info and post the actual spreadsheet with fake info.

 

[LosingMyMindAgain]

I attached the spreadsheet with my attempt at editing the macro. Ideally, I would like to take the data from sheet1 column N from N2 to whatever the last row is. Paste the unique counties in sheet2 column H from H3 down without the borders/fill color. I'd like to sort these in alphabetical order. Then in sheet2 column I I'd like the count of how many times each value appeared in sheet1 column N2 to last row. I'd like to keep the other data on the sheet that is controlled by simple countif Excel functions.

https://app.box.com/s/7ulj5fs9yf3brif7swoq