mapping Excel cell values

[Trudye]

I am very familiar with Access/vba and relatively familiar with VB 6.0. However I am not at all familiar with Excel/vba.

My question: I quest is to map cell values from one workbook to another. I have figured out how to open the 2nd workbook. But I cannot figure out how to map the cell values. Can anyone point me in the right direction?

Thank you,
Trudye

 

[PHV]

What do you mean with "map the cell values".
Something like the VLookup function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

For example copy the values from cell C1 in workbook2 to cell B5 in workbook1.

 

[cLFlaVA]

Try this:

wbkOne.Sheets("Sheet1").Cells(3, 1).Copy Destination:=wbkTwo.Sheets("Sheet1").Cells(5, 2)

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[PHV]

Something like this ?
Workbooks(2).ActiveSheet.Range("B5") = Workbooks(1).ActiveSheet.Range("C1")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Thank you both so much I will try your suggestions when I get to work.

Be well,
Trudye

 

[Trudye]

Thanks so much for your help but I have a few more questions.

I am referencing the workbook1 as DISPOS which is the name of the workbook (i.e. DISPOS.xls) DISPOS.sheets... and workbook2 as Activeworkbook.sheets(sheets).... Is that ok?

The counts have to be entered in different cols on different days (i.e. monday = col C; tuesday = col D etc.). I have added a DatePart function to my procedure to determine the day of the week. But how do I reference the cell based on the day of week value? Also there is not range either for the sending workbook or for destination the workbook. It is cell DISPO("Sheet1").Cells(B2)to a specific cell in the destination workbook.

And last (but not least) can I use this event:
Private Sub Workbook_open()

I ready that it is suppose to trigger when the workbook is opened. Is that true?

Thank you,
Trudye

 

[Trudye]

I think I may have figured it out.

From Cell B2 to Cell wkday.16
DISPOS.Sheets("Sheet1").Cells(2, 2).Copy Destination:=ActiveWorkbook.Sheets("Sheet1").Cells(16, WkDay)

Am I at least close?

Trudye

 

[cLFlaVA]

Keep in mind that "ActiveWorkbook" only works if the workbook is, in fact, the ActiveWorkbook.

Try to reference each workbook explicitly, ie,

Dim DISPOS As Workbook
Dim otherBook As Workbook

Set DISPOS = Workbooks("DISPOS")
Set otherBook = Workbooks("Book2")

DISPOS.Sheets("Sheet1").Cells(2, 2).Copy Destination:=otherBook.Sheets("Sheet1").Cells(16, WkDay)

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Trudye]

Thanks *cLFlaVA for the tip. That tip brings up another issue. There is a different worksheet for every week. Should I be designating activeworksheet as well?

There is also a Master Worksheet. If I put the code behind the Master Worksheet then using activeworksheet would not be necessary, right? Because the code would be in the template (Master) and thus behind every copy of the Master.

Also whenever I open the workbook I get the msg:
c:\.....\RSD_Report.xsl contains macros.

Is there a setwarnings, or echo type command that will stop this msg from showing up?

Thank again
Trudye

 

[PHV]

Is there a setwarnings, or echo type command that will stop this msg from showing up?
The macro security level can't be changed by code (for obvious reasons).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244