Copy worksheet ---> worksheet
I have 3 Worksheets in 1 Workbook....
the worksheets are known as...:
each worksheet has at least 2 columns that will have the sam
information as the others..for example:
Manifest has 1 column (A) named Receipt Number, Deliveries and Stuffin
also have 1 column that has this column as well.
My question is, what formula do I need to apply so that when
introduce the Receipt Number in Manifest's Receipt Number column i
will automatically be introduced in Deliveries and Stuffing's Receip
Number Column? That way I dont have to be intro...Open a message box if a worksheet is opened
This is a 2 parter. I need a simple macro that states how many times
a worksheet has been opened today. I want the results to reflect in
cell A1. Then when A1>0 (as the sheet is opened), I need a message
box to open to display a simple sentence which can then be closed by
clicking an OK button. Here's thanking you in advance.
Private Sub Worksheet_Activate()
If .Value > 0 Then
MsgBox "Sheet opened " & .Value & " times", vbOKOnly
.Value = .Value + 1
End Wit...Display worksheet with prompt/button
I was wondering if there is a way to have Excel prompt the user with
question, then open up a worksheet based on the answer to tha
For example, when Excel opens it could ask the user to make th
appropriate choice about what part of the workbook they want to use
then open up the corresponding worksheet....ie. if the user wants th
"apples" worksheet they could select the apples button and if they wan
the "oranges" worksheet they could select the oranges button and woul
be taken to the appropriate worksheet based on the button they select
Message posted from h...How do I setup a worksheet to track gas mileage/gallon?
I want to keep track of how much gas I'm using per gallon in my truck.
You can get as complicated as you want doing this but in the simplest terms
it can be done with a couple of columns of data:-
start miles end miles Distance Fuel added mpg
1000 3000 2000 100 20
Distance is a formula = B2-A2
mpg is also a formula =C2/D2
Start miles is taken from you odometer
Fuel added is what you put in at the pump
So the next time you fuel up, start miles would be 3000 and end miles would
be you odometer reading and you would drag the formula down.
I understand it is difficult to protect vba code. What Im looking for is all
possible methods that could be used to make it very difficult to access the
VBA code. I plan on releasing a template as trialware and plan on using
various protection. I am intrested in any methods that can slow the process
or create obsticles to prevent the changing of code.
with VBA: no real way to protect it. Nearly everything can be circumvented
in a couple of minutes. Only secure solution: Use COM addins (created with
VB or another programming language)
"...Pictures in Cells
I teach elementary computers, and I often make my own online worksheets
for the kids. I am making an "ABC Order" sheet where I have pictures of
animals in one column and pictures of animal names (as opposed to just
typing the names in the cells) in another column. The kids will slide the
names into alphabetical order, then slide the picture of the animal next to
My question is this; is there a way to "protect" or "lock" the pictures
so that the kids can move them but not accidentally resize them? I have
tried every combination of loc...VBA copy cell to another worksheet
Hello, I am trying to copy text information from one worksheet to
I open only the first sheet then I have a comand button by where I
want to say transfer the values.
La macro give an error at ObjWorshett.Cells(Riga, 1).text =
Errore di run-time '1004'.
Errore definito dall'applicazione o dall'oggetto.
Thanks in advance!
Dim oDO As New DataObject
Dim ObjWorshett As Worksheet
Dim strNomeFile As String
strNomeFile = "\\srv01\Dp\ANTONELLA\sblocco.xls"
Set ObjWorshett = Appli...Updating Excel Worksheet
I have an Excel worksheet that I created from a flat file
and then added some other information. Is there any way
to take a new flat file (updated info) and merge it into
the worksheet? If so how would I deal with dups.
...Viewing Worksheets withing One Workbook
My PC Excel (2002) allows me to have many worksheets within one workbook. I
can see the tabs at the bottom of the workbook to access each sheet. Does it
work the same in the Mac 2004 Excel? The MAC person I am sending my PC
workbook to says he can only see one worksheet when there should be four. He
doesn't see any tabs at the bottom showing the different sheet names. What
can I have him look for to find these sheets? Thanks once again :)
No Tabs can be seen?
In Excel for Windows you would go to Tools>Options>View> and checkmark "Sheet
Tabs" or hit CTRL + P...Very Slow Worksheet Calculation
I have code that runs on activation of a sheet to unprotect it, filter out
zero rows, and reprotect. Since I added this code to the workbook it
calculates very slowly - not only the sheet it runs on, but all sheets. I am
fairly new to use of VBA. See code:
Private Sub Worksheet_Activate()
' Autofilter Macro
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveSheet.Range("$A$1:$N$74").Autofilter Field:=1, Criteria1:="<>0", _
Oper...Copying rows identified by countifs function to seperate worksheet
In excel 2007 is there a way of copying rows identified by the countifs
function, into a seperate sheet
A B C
1 John Yes Top
2 Peter No Bottom
3 Jack Yes Top
4 Paul No Bottom
5 Mike No Top
6 Harry Yes Bottom
If I had the following function: =countifs(B:B, "Yes", C:...Frozen worksheet section to show on all other worksheets
I have created a "Navigation" tab that has buttons using macros that take you
to all the other tabs in my workbook. I used VBA code to hide all my tabs
except for the Navigation one. This way I can click on button that take you
to the tab but its all seamless to the viewer and makes it more like a
The problem is that I have to keep going back to the dashboard to choose
another tab or add all the buttons to every tab.
Is there a way to permanently split the windows or show that appropriate
area of the navigation page at the top of every tab so i have all...SSL protection won't work with other than port 25
We have problems with secured smtp connections. ISPs in Finland block port
25 to outside smtp server, so we are using port 7725 as secondary port to
smtp. I made proper configurations to smtp virtual server so that it listens
also 7725. This configuration works when I dont' user secured connections,
but when I enable ssl protection in outlook it doesn't work, port 25 works
same time as it should.
I tried to test smtp with telnet to port 7725 and it responded fine if ssl
wasn't required (and I could sent mails), and if I put ssl required mode on,
it asked starttls as it...How? compare two worksheets and identify rows of data that appear on both.
Suppose I have 2 worksheets.
Sheet 1 has 1000 lines of data spanning xNumber of columns.
Sheet 2 has 500 lines of data, also spanning the same number of columns.
An unknown number of identical lines of data appear on both sheets.
How can I get to a point where I can build one worksheet that is a composite
of both sheets - such that no identical line of data appears twice on the
Thanks in advance, Simon.
You could do this several ways. It really depends on your data
integrity and what sort of data it is.
-- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row...Populate cell with data from another worksheet
I would like to flag a cell with an A for active or I for inactve (lets say
cell A1) and based on the entry have another cell (G1) populate an entry
from another worksheet. The catch being that it will be dynamic in the sense
that the data retrieved can be reused if it ever becomes inactive again.
So, my dilema is when a A1 is marked active I not only want to populate the
cell with data from Sheet2 I also want the cell used on Sheet2 to be made
unavailable for future use. When A1 is marke inactive I want the
corresponding cell in Sheet2 made available again.
...Can I move a button from toolbar to a worksheet?
I need to move or copy filter Reapply button [Data\Sort&Filter
\Reapply] from toolbar to my worksheet. Is it possible?
You could add that button to your QAT
I don't think you can drag it to the sheet.
Gord Dibben MS Excel MVP
On Wed, 21 Apr 2010 06:56:56 -0700 (PDT), Arty <firstname.lastname@example.org> wrote:
>I need to move or copy filter Reapply button [Data\Sort&Filter
>\Reapply] from toolbar to my worksheet. Is it possible?
I have 12 worksheets, one for each month, in the same spreadsheet file. I
have formatted the first one, but how can I copy the formatting (ONLY the
formatting) through to the others without having to do each one separately?
If you click the intersection between the row and column identifiers in
your formatted sheet, this will highlight the complete worksheet. Then
click the Format Painter icon, click the second sheet tab and click in
cell A1. Repeat this ten times.
...Selecting Print area in protected worksheet
I have a worksheet where the users enter data, new rows etc. The calculations
are at the bottom and I have protected them. However, when I do this the
users can no longer set the print area to print. How do I solve this?
DRB, I can set the print area with the sheet protected and or the workbook
protected in 2003 with no problems ???
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"DRB" <DRB@discussions.microsoft.com>...Opening Lotus password protected file
I'm trying to convert some of my old lotus files that are
password protected into Excel files. My old Lotus program
got corrupted somehow so I can't remove the password thru
Lotus. Any ideas on how I might make the conversion?
Would appreciate any help. Thank you.
...Worksheet menu Bar Missing
In customize tools the option to select or deselect the above is missing.
Anyone know how to restore it back
hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this in and hit enter
Application.CommandBars("worksheet menu bar").Enabled = True
back to excel and test it out.
If it didn't work, you can kill your existing toolbar file and it'll reset every
customization you've ever made.
windows start button|find/search for *.xlb
rename them to *.xlbOLD
Back to excel to see if everything is back to norma...How do I copy a worksheet from a workbook as csv file
I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
Try this one to save the file in C:\
Dim wb As Workbook
Dim strdate As String
Dim Fname As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"
Application.ScreenUpdating = False
Set wb = ActiveWorkbook
W...Protection / Grouping
Is there any option that I can select when protecting a worksheet that will
allow the user to still use the outlining buttons?
I am using XL 2003 for this project.
"Paul W Smith" <pws@NOSPAM.twelve.me.uk> wrote in message
> Is there any option that I can select when protecting a worksheet that
> will allow the user to still use the outlining buttons?
> Paul Smith
If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (au...protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this?
select the cells for which you want to allow entries. After this goto
'Format - cells - Protection&...Worksheet is compacted
I'm not sure how to explain this. I have a Budget wooksheet that now
only has 5 columns on it. Somehow about a year ago I removed the
blanks to the right. Now of course I'd like the sheet back to normal.
If I copy the cells all formatting is lost. What options do I have
aside from a reconstruction effort? Please help.
These columns are hidden.
Press CTRL +A
Select Format | Columns | Unhide
to show all columns again.
More Excel ? www.rosenkrantz.nl or email@example.com
-----------------------------------------------------------------...Macro to copy & paste-special-values data to selected worksheets
I have a workbook containing worksheets called “List-1”, “List-2”, “List-3”,
“List-4” and many other worksheet “templates”. I need a macro to perform the
1. On any of the particular worksheet templates, copy the data on the
entire row for all rows within the range A1:H50 only if the value for that
row in column H is greater than 0, and then “paste special values” the data
on the worksheet called “List-1” starting at cell A1. In other words, for any
row within the range that has a value in column H that is greater than 0, the
entire row of data in column A-H will be...