setting values in a sheet without changing the focus

deadseasquirrels

Board Regular
Joined
Dec 30, 2004
Messages
232
If I have multiple sheets up, and I want to change the value on one sheet. Is there a way to do that in VBA without changing the focus of the active window with Window('name').activate.

I have a lot of values where I have to go back and forth with, and I'd rather try to update the info without changing focus back and forth.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
Yes, this is easily done.

Example:
Code:
Sub Change_Value()
Sheets("Sheet2").Range("A1").Value = "Test"
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,946
Office Version
  1. 365
Platform
  1. Windows
Can you post the code you have?

Generally you don't need to select/activate workbooks/worksheets/ranges etc to work with them.

For example this will put X in A1 on Sheet1 in workbook MyWorkbook.
Code:
Workbooks("MyWorkbook.xls").Worksheets("Sheet1").Range("A1") = "X"
 

deadseasquirrels

Board Regular
Joined
Dec 30, 2004
Messages
232
Thanks for the suggestion. I won't post my code because it's two different modules, and two different forms a lot of code that isn't very useful to show. Right now it's a form that fills in another form, and I'm adding a billing component to that form. I'll try out the suggestion. I was actually considering saving all pertinent information into an object and then putting all that information into the billing form in one shot instead of going back and forth. I'll try both ways.
 

will_simpson

New Member
Joined
Oct 17, 2006
Messages
29

ADVERTISEMENT

Try something similar to this?

Workbooks("Your_wb1.xls").Worksheets("Sheet1").Range("A1")=Workbooks("Your_wb2.xls").Worksheets("Sheet1").Range("A1")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,946
Office Version
  1. 365
Platform
  1. Windows
Are you sure it wouldn't be helpful to post the code?

Without seeing it we really are just guessing.:)
 

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
Have you thought of using a Userform for this. It sounds like it would be your best bet.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,374
Members
410,911
Latest member
AniEx
Top