VB for applications Grab data from another worksheet in Excel 1

[pcb105]

Hi,

This is probably a real easy question. Sorry I am a newbie.

I have a workbook that has multiple sheets in it. I would like to run a VB macro that reads data from one sheet and then updates data on another sheet.

I found that using thew following command
Sheets("Sheet2").Select
GrabData=Cells(row,column).value
grabbing the data then going back to the orginal sheet
Sheets("Sheet1").Select

Works....BUT it causes a flash on the screen when I run the script. Is there a better / easier way of doing this?

Thanks,

-Paul

 

[SkipVought]

Hi,

   GrabData=Sheets("Sheet2").Cells(row,column).value 
'grabbing the data then going back to the orginal sheet 
   Sheets("Sheet1").Cells(anotherrow, anothercol).value = GrabData
[code]
or 
[code]
   Sheets("Sheet2").Cells(row,column).copy  Sheets("Sheet1").Cells(anotherrow, anothercol)
[code]

avoid using the Select & activate methods.

Skip,
[sub]
[glasses] [b][red][/red][/b]
[b][/b] [tongue][/sub]

 

[duGly]

I like Skip's solutions. You can also avoid using the variable altogether (and make your code shorter) with the following single line of code:

Sheets("Sheet1").Cells(Row, Column) = Sheets("Sheet2").Cells(Row, Column)

Or, if you have the cells named,

[cellName1] = [cellName2]

[red]— Artificial intelligence is no match for natural stupidity.[/red]

 

[cresbydotcom]

remember to activate the other workbook

Workbooks.Item("blahblah").Activate

and then assign the value

yourvariableorcell = ActiveWorkbook.ActiveSheet.Range("A1").Value (or .Text)

 

[duGly]

Actually, as Skip pointed out, selecting or activating are NOT necessary. In fact, that is what causes the screen flash. Selecting and activating greatly slow down the code. It is almost always preferable to assign the values directly, rather than selecting or activating.

Also, there is no workbook to activate in Paul's example since he is just writing values from one worksheet to another within the same workbook.

Hope this helps!

[red]— Artificial intelligence is no match for natural stupidity.[/red]

 

[jakecam]

Write the following command first within your sub:

application.screenupdating = False

 

[pcb105]

Hi All,

Thank you for your help. I just found out that I was not following proper courteous rules for posting and accepting help on this message board. I am sorry that I offended some of you by not doing this.

Thanks again for the help.

-Paul