Posts by lightguy101

Hi Guys,

I'm using the below to count how many times a word shows up in a column, I would however like to be able to have all this counting happen on the main page and look for individual words in single columns on different sheets.

Any advice on this would be great, thanks in advance and see attached.

=COUNTIF($C$2:$C$200,"New Zealand")

Sure thing thanks again for your help.

Hi Guys, Wow this is amazing and much fast thanks so much for your help.

What I am trying to do now is create a total on my main page that totals up the customers in each month be state. For look at the Sheet name, then the state and then report the total back on the main page. What would your thoughts be about how to go about this, I've played around with a few things, ideally this would report this back into a cell.

Hi Gijsmo,

Wow thanks so much getting closer now, i have added the extra copy information for the other months through to December and noticed it only goes as far as April. It will continue to copy the other months but only if i put for example "May" in column F on the companies tab, putting "September / January / May" will result in nothing been copied to May or September sheets yet it pulls through to the January tab without any issues. Is there a limitation on how much can be done at a time with the ELSEIF?

I have attached the edited workbook, thanks again for your help I'm very grateful for your skill.

Wow so simple thanks so much works perfectly, what would be the simplest way Gijsmo to clear out each sheet before inserting the copied cells except for the top line?

Hi Gijsmo,

With the code changes made unfortunately nothing pulls across to the other sheets.

Sub NewCOPY()

Dim c As Range
Dim Source As Worksheet
Dim Target As Worksheet
Dim Target1 As Worksheet

Set Source = ActiveWorkbook.Worksheets("Companies")
Set Target = ActiveWorkbook.Worksheets("January")
Set Target1 = ActiveWorkbook.Worksheets("February")

For Each c In Source.Range("F1:F" & Source.Cells(Rows.Count, 1).End(xlUp).Row)
   If c = "*January*" Then
      c.EntireRow.Copy
      Target.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   ElseIf c = "*February*" Then
      c.EntireRow.Copy
      Target1.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   End If
Next c
End Sub

Hi Roy,

Thanks for your feedback and see attached.

Hi VB Experts,

I have the below code working well however what I have in my column F on my main tab is different months, what happens is when column F contains as an example January it would then copy that whole line to another sheet. Column F will contain multiple months for example "January / May / September", what i would like to do is have it move when say the cell contains "January" in the string rather than needing the whole "January / May / September" as sometimes the months are in a different order. It would seem like it currently only moves it to another sheet when it is an exact match rather than containing "January"

Any help would be amazing and thanks in advance.

Sub NewCOPY()


Dim c As Range
Dim Source As Worksheet
Dim Target As Worksheet
Dim Target1 As Worksheet


Set Source = ActiveWorkbook.Worksheets("Companies")
Set Target = ActiveWorkbook.Worksheets("January")
Set Target1 = ActiveWorkbook.Worksheets("February")


For Each c In Source.Range("F1:F" & Source.Cells(Rows.Count, 1).End(xlUp).Row)
If c = "January" Then
c.EntireRow.Copy
Target.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
ElseIf c = "February" Then
c.EntireRow.Copy
Target1.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next c
End Sub