I pick up your remarks about sheaves. Indeed, the sheaf condition is a very good example to get a geometric idea of a limit.
Assume that $X$ is a set and $X_i$ are subsets of $X$ whose union is $X$. Then it is clear how to characterize functions on $X$: These are simply functions on the $X_i$ which agree on the overlaps $X_i \cap X_j$. This can be formulated in a fancy way: Let $J$ be the category whose objects are the indices $i$ and pairs of such indices $(i,j)$. It should be a preorder and we have the morphisms $(i,j) \to i, (i,j) \to j$. Consider the diagram $J \to Set$, which is given by $i \mapsto X_i, (i,j) \mapsto X_i \cap X_j$. What we have remarked above says exactly that $X$ is the colimit of this diagram! In a similar fashion, open coverings can be understood as colimits in the category of topological spaces, ringed spaces or schemes. It's all about gluing morphisms.
Now what about limits? I think it is important first to understand limits in the category of sets. If $F : J \to Set$ is a small diagram, then we can consider simply the set of "compatible elements in the image" of $F$, namely
$X = \{x \in \prod_j F(j) : \forall i \to j : x_j = F(i \to j)(x_i)\}$.
A short definition would be $X = Cone(*,F)$. Observe that we have projections $X \to F(j), x \mapsto x_j$ and with these $X$ is the limit of $F$. Now the Yoneda-Lemma or just the definition of a limit tells you how you can think of a limit in an arbitrary category: That $X$ is a limit of a diagram $F : J \to C$ amounts to say that elements of $X$ .. erm we don't have any elements, so let's say morphisms $Y \to X$, naturally correspond to compatible elem... erm morphisms $Y \to F(i)$. In other words, for every $Y$, $X(Y)$ is the set-theoretic limit of the diagramm $F(Y)$. I hope that this makes clear that the concept of limits in arbitrary categories is already visible in the category of sets.
Now let $X$ be a topological space and $O(X)$ the category of open subsets of $X$; it's an preorder with respect to the inclusion. Thus a presheaf is just a functor $F$ from $O(X)^{op}$ to the category of sets (or which suitable category you like). Now open coverings can be described as certain limits in $O(X)^{op}$, i.e. colimits in $O(X)$, as above. Observe that $F$ is a sheaf if and only if $F$ preserves these limits: If $U$ is covered by $U_i$, then $F(U)$ should be the limit of the $F(U_i), F(U_i \cap U_j)$ with transition maps $F(U_i) \to F(U_i \cap U_j), F(U_j) \to F(U_i \cap U_j)$, i.e. $F(U)$ consists of compatible elements of the $F(U_i)$, meaning that the elements of $F(U_i)$ and $F(U_j)$ restrict to the same element in $F(U_i \cap U_j)$. Thus we have a perfect geometric example of a limit: the set of sections on an open set is the limit of the set of sections on the open subsets of a covering.
Somehow this view takes over to the general case: Let $F : J \to Set$ be a functor. Regard it as a presheaf on $J^{op}$, and he map induced by $i \to j$ in $J^{op}$ as a restriction $F(j) \to F(i)$. Also call the elements of $F(i)$ sections on $i$. Then the limit of $F$ consists of compatible sections. Since I've been learning algebraic geometry, I almost always think of limits in this way.
"...the sheaf condition on a presheaf can be expressed as stating that the contravariant functor takes colimits to limits"
This is not correct. The reason is that the index category can be rather wild and colimits in preorders don't care about that. In detail: Let $U : J \to O(X)^{op}$ be a small diagram. Then the limit is just the union $V$ of $U_j$. Thus $F$ preserves this limit iff sections on $V$ are sections on the $U_j$ which are compatible with respect to the restriction morphisms given by $U$. If $J$ is discrete and $U$ maps everything to the same open subset $V$ of $X$, then the compatible sections are $F(V)^J$, which is bigger than $F(V)$.