Excel macros?

Question

Hi,
i have some questions about macro code for excel. I'm not very familiar with vb so it may be very simple but here are the 2 Questions.

Part of my macro code is "Sheets("Today").Select" But i want to place instead of "today" which is the sheet name, i want to place a variable lets say m which will be the place holder for the sheet name so i can keep changing the sheets it selects.

Second, how can i tell my macro to place text in a cell, because when i try
ActiveCell.Text = "Item#" does not work and i want to place "Item#" in the active cell.
Thank you

Answer 1

The first answerer gave you some good points. At the beginning it would be a good idea to delclare a variable as a string to replace the Today that is in quotes. Since SheetName is a variable it doesn't get put in quotes like Today does since it is the exact text.

Dim SheetName As String

SheetName = "Today"
Sheets(SheetName).Select

And like the first answerer stated, for the ActiveCell you want to use the Value property or FormulaR1C1 property instead of the text Property to set what the active cell equals.

So you'd either put:

ActiveCell.Value = "Item#"

or

ActiveCell.FormulaR1C1 = "Item#"

The value property directly changes the value. The FormulaR1C1 property sets or reads what is displayed in the formula bar for that cell.

The Text property is only a readable property. The Text property tells you what text is displayed in the cell or what text is displayed in a textbox or combobox, etc. In some situations its better to use the Text property to get the text in the cell and evaluate it. But you cannot try changing the text in the cell using the Text property.

Another good thing to know is that for every worksheet there are actually two worksheet names that are used to keep track of that worksheet. If you go to the Visual Basice Editor screen and look in the Project window you'll see all of the worksheets listed there. The worksheet names that you see on the tabs will be in parenthesis. The names that Excel uses to keep track of the worksheets even when the tab names are changed are the names not in parenthesis. So for instance you might see this in the project window:

Sheet3 (Today)

You could also select the Today worksheet by stating:

Sheet3.Select

In some ways this is a better method in the event that someone changes the tab name. Excel will still reference the same sheet even though the tab name was changed.

Another way of referencing the worksheets is by index number. The sheet farthest to the left is 1, the next 2, and so on. So if you want to select the first sheet (the one farthest to the left) you could state:

Sheets(1).Select

To select the third sheet you could state:

Sheets(3).Select

One thing to keep in mind is that the Sheets collection includes all of the sheets including Chart sheets. If you don't want to include chart sheets, use the Worksheets collection instead. So you might want to use:

Worksheets(3).Select

If you are starting to learn macros and VBA for Excel. I sincerely suggest you getting the book:

Excel 2003 Power Programming with VBA
by John Walkenbach

It is very well written and goes through things step by step. I wish I would have had it when I first started learning VBA for Excel.

As you get better with creating macros in Excel, you'll want to create your macros so you can avoid using the ActiveCell or Selecting stuff unless you absolutely have to. It can slow the speed of the macro down and makes it perform functions it doesn't necessarily have to.

Another thing that comes in handy is turning the screen updating off. After you declare the variables, it is often a good idea to declare:

Application.ScreenUpdating = False

This forces Excel to wait to update the screen until all of the calculations and stuff are made. That way you don't have the screen flashing like crazy when the macro runs and the macro should run quicker. The only time you want it set to true is if you have to have the user input something with an input box or a message box. Then you want to turn the screen updating back on before that happens.

Some other handy things you might want to know are the properties:

.CurrentRegion - this selects the group of cells around the cell you reference

.UsedRange - this selects the block of all of thethe cells that have been used while editing that worksheet.

Another good thing to learn about is the Immediate Window in the Visual Basic Editor (Ctrl + G will display it) and using Debug.Print to show variable values in the Immediate Window. It's a good debugging tool.

Answer 2

you are able to convert pdf to excel format, however the macro VBA won't artwork interior the converted excel. in simple terms use a pdf to excel converter, google it, you will discover many, for my area making use of simpo pdf to excel, you will possibly be able to provide it a attempt.

Answer 3

Dim Sheetname as String

...
Sheetname = "Sheet3"
Worksheets(Sheetname).select
...
Activecell.value = "item#"

...