Pasting TextBox values
Hi Patrick,
1. Is there a shorter way to select all worksheets?
Yes: Sheets.Select.
Unfortunately, unlike in Excel, VBA requires each sheet to be addressed
separately.
Try, therefore, something like:
'=================
Sub Test03()
Dim SH As Worksheet
Dim rng As Range
Const strAdd As String = "A1301"
For Each SH In ActiveWorkbook.Worksheets
Set rng = SH.Range(strAdd)
rng(1, 1).Value = TextBox3.Text
rng(1, 2).Value = TextBox5.Text
Next SH
End Sub
'<<=================
---
Regards,
Norman
"Patrick Simonds" wrote in message
...
In the code below I am attempting to select all of my worksheets, then
select cell A1301 and past the data from textboxes into cell A1301 and
B1301 of all the Worksheets. What happens is that all of the worksheets
are selected and cell A1301 is selected on all the worksheets but the data
is only placed on the 1st worksheet (June - August). So two questions:
1. Is there a shorter way to select all worksheets?
2. How can I paste the TextBox values to all of the worksheets?
Dim rng
Set rng = Cells(ActiveCell.Row, 1)
Sheets(Array("June - August", "September - November", "December -
February", _
"March - May", "Annual")).Select
Sheets("June - August").Activate
Range("A1301").Select
rng(1, 1).Value = TextBox3.Text
rng(1, 2).Value = TextBox5.Text
|