VB formula help

lightguy101 · Apr 22nd 2022

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.

Code

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

Display More

**royUK** · Apr 22nd 2022

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Apr 22nd 2022

Attach an example workbook, but it is far better to keep all data on one sheet.

lightguy101 · Apr 22nd 2022

Hi Roy,

Thanks for your feedback and see attached.

gijsmo · Apr 23rd 2022

Maybe instead of

Code

If c = "January" Then

try

Code

If c Like "*January*" Then

and likewise for other months

lightguy101 · Apr 23rd 2022

Hi Gijsmo,

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

Code

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

Display More

gijsmo · Apr 23rd 2022

The codeword is "Like", you still have "=" in the code

Code

If c Like "*January*" Then

NOT

Code

If c = "*January*" Then

lightguy101 · Apr 23rd 2022

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?

gijsmo · Apr 24th 2022

There's a bunch of ways to do this. One way is in the attached revised copy of your original workbook.

I made a few other changes as well to streamline the code a bit.

I also added a Clear Data button to the Main sheet if you want the option to do this in one go - as the name implies this will clear the data from all the "Month" sheets (except the header row).

Customer Call Plan 18-4-22 v2.xlsm

lightguy101 · Apr 24th 2022

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.

gijsmo · Apr 24th 2022

The loop in the NewCopy macro that has the "Like" statements in it is created as a series of "ElseIf"s.

This means that, for example, a Call Month value of "December / April / August" will only copy to April because April gets tested before August or December.

If you want to copy data for a Call Month value of "December / April / August" to each of the December, April & August sheets then the loop needs to be redesigned. The attached version does this. Bear in mind that this loop now takes a longer to run and it has to test each row 12 times for each month, not skip to the next row like it did before when one of the ElseIf conditions was met.

Customer Call Plan 18-4-22 v3.xlsm

**royUK** · Apr 24th 2022

The most efficient way is to forget splitting the data and simply use AutoFilter to view the required month.

Rather than using Loops to check the dates AutoFilter could be automated with VBA to split the data. If the data is likely to change the the code could clear the destination sheet then split the data. Again the most efficient way.

gijsmo · Apr 24th 2022

Roy is, as always, correct !

AutoFilter is a much faster way to do this type of copying between sheets.

If you want to use that instead of looping, revised version using AutoFilter is attached.

Customer Call Plan 18-4-22 v4.xlsm

lightguy101 · Apr 25th 2022

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.

gijsmo · Apr 25th 2022

This is clearly an extension of the original request.

May be best to start a new thread and also include a sample spreadsheet with a mock-up of the final result you are trying to achieve.

lightguy101 · Apr 25th 2022

Sure thing thanks again for your help.

VB formula help

lightguy101 Apr 25th 2022

Participate now!

Share

Similar Threads

VBA for copying Data in Sheets added by hyperlinks

Missing Something Simple Retrieving Value in Active Cell and Assigning it to a Variable in VBA - Please Help

Extrapolation Macro VBA

VBA Formula

VBA Error 13 and 2015 with Vlookup in Evaluate

Help with vba code please