Newbie Q : With VBA Read cells from unopened sheet

  • Thread starter Thread starter avatar
  • Start date Start date
A

avatar

Hello People :)

When u might think when all below has been read .. D*mn he's a newbie
:) Your correct. :)

So now on for my question.

I have 2 sheets.

In sheet 1 i have

sheet x witch has the folowing info on it
Kolom A : names ( A1 to x )
Kolom B : job discription
Kolom C : Cost price p/hour

In sheet 2 i want this info from above without opening sheet 1 ( + some
other stuff from other sheets but that not rellevant now )

I can make a hard "copy" like

Formule = "='G:\Excelhelp\[Sheet 1]Week 1" + "'!D$75"
Range("C7").Select
ActiveCell.Value = Formule

But offcource i dont want this.
When i add a new name i dont want to make another hard copy in my macro
for that.

So what i need but i cant make yet is :

A1 to x ( read untill you find the first empty cell )
+ the same info on that row for cell B & C
copied to sheet 2 .


Can anyone help me figure out howto do this ?

I think you all dont really understand what i mean so if you have any
questions please ask :)

Thanks in advance !!!!!!!

Greetinx Avatar
 
Hi

Have you 2 workbooks, or 2 sheets?
Aniway the simplest way:

Into cell on sheet 2, enter '=', activate sheet x, and press Enter. You see
something like
='sheet x'!$A$1
when you have 2 sheets in same workbook, or
='[SourceWorkbook.xls]sheet x'!$A$1
when you have 2 sheets in diferent workbooks. When the last is the case,
closing the source workbook adds path to workbook name, and the link remains
functional.

As you want links to range of cells, change the refernces to relative
='sheet x'!A1
or
='[SourceWorkbook.xls]sheet x'!A1

and add checking for empty cell (otherwise 0 is returned instead nothing)
=IF('sheet x'!A1="","",'sheet x'!A1)
or
=IF('[SourceWorkbook.xls]sheet x'!A1="","",'[SourceWorkbook.xls]sheet x'!A1)

and copy the formula to range A:C



Arvi Laanemets
 
Back
Top