Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VB for applications Grab data from another worksheet in Excel 1

Status
Not open for further replies.

pcb105

Technical User
Apr 25, 2007
11
US
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
 




Hi,
Code:
   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]
 
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:
Code:
Sheets("Sheet1").Cells(Row, Column) = Sheets("Sheet2").Cells(Row, Column)
Or, if you have the cells named,
Code:
[cellName1] = [cellName2]

[red][banghead]— Artificial intelligence is no match for natural stupidity.[/red]
 
remember to activate the other workbook

Workbooks.Item("blahblah").Activate

and then assign the value

yourvariableorcell = ActiveWorkbook.ActiveSheet.Range("A1").Value (or .Text)
 
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][banghead]— Artificial intelligence is no match for natural stupidity.[/red]
 
Write the following command first within your sub:

application.screenupdating = False
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top