Execute code based on specific text in target range of cells

Re: Execute code based on specific text in target range of cells

Brand_New,

Please keep the following in mind the giving titles to your threads:

Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.

• The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
• The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
• The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

Your title of "[COLOR="red"]Specifying a recorder Macro[/COLOR]" does not describe your thread or objective and is not helpful to those searching the forum for a solution to a similar need.

[COLOR="darkred"]Please note the change to your title, which is based on the objective stated in your thread.[/COLOR]

If the new title still does not accurately describe your thread you may make further edits as needed per the above guidelines.

Re: Execute code based on specific text in target range of cells

I remember being brand new to VBA! Sometimes I think I'm not much further along!! But let's see what we can do here.

First, every macro starts with "Sub YourMacroName" and ends with "End Sub". You copied over the "End Sub", but not the "Sub YourMacroName". So now we're left to wonder what's happened before the bit of code you posted, and that could make a difference in what you need.

Second, when you say "if the word "published" is in that specific region", what "region" are you talking about? In the cells you've selected to copy? In the title of the worksheet you want to paste into? In a specfic cell in one of the worksheets?

In your macros, you'd be a lot better off working with worksheets and ranges as objects, rather than as selections. The Selection object is very useful, but most of the time it's not needed.

So let's try something. I'm making the following assumptions:
-- the code you posted is the whole macro, just without the beginning line
-- you are cutting from the "Upcoming Workflow" sheet
-- "Publised" was a typo in your post, and your worksheet name is really spelled correctly as "Published"

When you're a bit more specific with what "region" you need to see "Published" in, we can deal with that.

Ed

Sub MyPasteMacro()
 
Dim wkbWorkflow   As Workbook   'This is your entire workbook
Dim wksUpcoming   As Worksheet  'Upcoming Workflow worksheet
Dim wksPublished  As Worksheet  'Published Workflow worksheet
Dim rngUpcoming   As Range      'Range to cut from Upcoming
Dim rngPublished  As Range      'Range in which to paste in Published
Dim lngLastRow    As Long       'Row in whcih to paste in Published
 
'I am making the assumption that you are manually identifying
'and selecting the range that needs to be cut from the
'"Published Workflow" worksheet
 
'Initialize workbook and worksheet objects
Set wkbWorkflow = ActiveWorkbook
Set wksUpcoming = wkbWorkflow.Worksheets("Upcoming Workflow")
Set wksPublished = wkbWorkflow.Worksheets("Published Workflow")
 
'Set a range to the selected cells
Set rngUpcoming = Selection
 
'Identify the first blank row underneath the current
'text in the Published worksheet
'I'm assuming Column B
lngLastRow = wksPublished.Range("B65536").End(xlUp).Row
 
'Set the paste range
Set rngPublished = wksPublished.Cells(lngLastRow, 2)
 
'Copy
rngUpcoming.Copy
 
'Paste
rngPublished.PasteSpecial xlPasteAll
 
'Adjust column width
wksPublished.Columns("B:B").EntireColumn.AutoFit
 
'Delete cells
rngUpcoming.Delete Shift:=xlUp
 
End Sub

Re: Execute code based on specific text in target range of cells

I'm not going to have a lot of time to work with this until after the weekend. In the meantime, here's a few things for you to do:

-- In Module 1, every single one of your Button_Click modules has the same two variables dim'd. I'd suggest putting them at the top of the module in the Declarations area. (In the VBE, you'll see the name of your macro in a drop-down list at the top right of the work area. If you click the arrow to expand the list, you'll see "(Declarations)".) If you put your common variables there, you can avaoid re-declaring them in every macro.

-- Also, you have "Range("C5:C116").Value". There is no worksheet object attached to this range. That can be dangerous and lead to unpredictable results. You really need to set your range as a property of a specific worksheet. I would Dim a worksheet variable up in the Declarations area, and set it to the worksheet being worked on inside the button macro. Then adjust your Range codes so they become attached to that worksheet - something like "wksWorkflow.Range("C5:C116").Value".

-- I would reconstruct your button macros to be more compact and less confusing. The MyNote and Answer with the double IFs is a bit confusing. It's just as easy to do something like:

If MsgBox("Are you sure you want to clear May workflow?", _
vbQuestion + vbYesNo, "Clear Workflow Detail Alert") = vbYes Then
'Work with these ranges
Else
'Work with these ranges
End If

You've also just eliminated the MyNote and Answer variables.

And what is "Value = True" supposed to do? If it's meant to NOT clear the values, then simply eliminate the Else (or Answer = vbNo) portin of the code.

-- Give your buttons some descriptive names - something like btnClearMay, and put a comment or two in each button macro to tell you at a glance which one you're looking at and what it's going to do. (I couldn't access the properties for these buttons - how did you set them?)

-- Did you understand the code I gave you? Do you understand what it's trying to do? Can you see where it needs to be adjusted?

-- You said:

Quote

If--for instance-- on sheet 1 range b4:e4 has the word "published" written in it then i want the info in range c5:c10 to be copied into the next available queue on sheet 4

You do not have sheets named Sheet1, Sheet4, sheet 1, sheet 4, or anything resembling that. Please use your actual sheet names. And tell us which button needs to be clicked. It's all obvious to you, because you're in the middle of building this. But for anyone else, there's a lot that is not obvious. Using these specifics can really streamilne getting help.

Cheers!
Ed

Re: Execute code based on specific text in target range of cells

Quote

On Sheet 1 I have 3 main buttons. Right now the only issue I have is the "Published Workflow" button. I need the information that is cut from column C to be pasted into Sheet 4 under its respective month (according to the intended publication date"). Also I'd like --on sheet 3--for the information copied from column 3 to be pasted in sheet 1 under their respective month.

Is there a macro that can tell excel to paste the information being copied under the month indicated in the "intended publication date" queue?

Try using a named range for each month. Click in the cell for each month, type the name of the month in the address area just to the left of the formula bar, and hit enter. Now in VBA, you can access that range: Sheet1.Range("May") - .Row will give you the row that range sits in, and you can increment down from there to find the first empty spot, and .Column should give you the left-most column of your merged cells.

Does that make sense?
Ed

Re: Execute code based on specific text in target range of cells

First, please tell us what error you're getting and on what line of code. Otherwise, it's like going to the doctor and saying "It hurts!", but not saying where or how it hurts.

Second, let's look at how you're setting your objects.

Quote

Set wksSource = Sheet1
Set wksDestination = Sheet4

If you're setting a worksheet object by its name, then you need to put the name in quotes:
Set wksSource = "Sheet1"

Quote

If Range("B4,e4") = "published" Then
Range("b4:e11").Delete Shift:=xlUp

Range is a property of a worksheet (in this case). You can't use Range without qualifying which worksheet it belongs to.

With wksSource
.Range("B4") 'notice the . before Range
End With

wksSource.Range("B4")

Either one is valid.

Also,

Quote

If Range("B4,e4") = "published" Then

Should that be ("B4:E4"), with a colon instead of a comma?

Get in the habit of going up to Debug and using Compile VBA Project. It can highlight several of these issues before you get errors.

And don't give up! Can't always promise a _speedy_ answer, but you will get some answers. And I might get stuck myself 'cuz I'm not the top of the class by any means. But we'll get you there.

Ed

Re: Execute code based on specific text in target range of cells

I'll take a closer look tomorrow.
Right off the top, look at

Quote

Sheet1.Cells(Rows.Count, "c")

What Rows are you counting? From which worksheet? You and I understand Sheet1 - but Excel may not.
Sheet1.Cells(Sheet1.Rows.Count, "c") will keep you out of that trouble, at least.
Just remember - any property needs to be linked back to its object, or your results may not be what you expect.

Ed