VBA to copy sheets (as values) that begin with the same sheet name
 

Experienced in Excel, but I am just learning VBA.

I want the macro to call a combo box and then select all sheets that start
with the value in the combo. Then, it needs to copy the selected sheets (as
values) to a new workbook.
--
Thanks,
Brent

VBA to copy sheets (as values) that begin with the same sheet name
 

Assuming Sheet1 has the ComboBox:

Sub Test()
Dim ws As Worksheet, fFound As Boolean
fFound = True
For Each ws In Worksheets
If Left(ws.Name, Len(Sheet1.ComboBox1)) = Sheet1.ComboBox1 Then
ws.Select fFound
fFound = False
End If
Next
ActiveWindow.SelectedSheets.Copy
End Sub

Not sure what you mean by wanting a macro to call a combo box.

--

Vasant





"Brent Walker" wrote in message
...
Experienced in Excel, but I am just learning VBA.

I want the macro to call a combo box and then select all sheets that start
with the value in the combo. Then, it needs to copy the selected sheets
(as
values) to a new workbook.
--
Thanks,
Brent

VBA to copy sheets (as values) that begin with the same sheet
 

Thanks. This is helpful.

When I said I want the macro to call a combo box, I meant that I want the
macro to call a form that contains a combo box. Any way to do that?

"Vasant Nanavati" wrote:

Assuming Sheet1 has the ComboBox:

Sub Test()
Dim ws As Worksheet, fFound As Boolean
fFound = True
For Each ws In Worksheets
If Left(ws.Name, Len(Sheet1.ComboBox1)) = Sheet1.ComboBox1 Then
ws.Select fFound
fFound = False
End If
Next
ActiveWindow.SelectedSheets.Copy
End Sub

Not sure what you mean by wanting a macro to call a combo box.

--

Vasant





"Brent Walker" wrote in message
...
Experienced in Excel, but I am just learning VBA.

I want the macro to call a combo box and then select all sheets that start
with the value in the combo. Then, it needs to copy the selected sheets
(as
values) to a new workbook.
--
Thanks,
Brent

VBA to copy sheets (as values) that begin with the same sheet
 

UserForm1.Show

--

Vasant

"Brent Walker" wrote in message
...
Thanks. This is helpful.

When I said I want the macro to call a combo box, I meant that I want the
macro to call a form that contains a combo box. Any way to do that?

"Vasant Nanavati" wrote:

Assuming Sheet1 has the ComboBox:

Sub Test()
Dim ws As Worksheet, fFound As Boolean
fFound = True
For Each ws In Worksheets
If Left(ws.Name, Len(Sheet1.ComboBox1)) = Sheet1.ComboBox1 Then
ws.Select fFound
fFound = False
End If
Next
ActiveWindow.SelectedSheets.Copy
End Sub

Not sure what you mean by wanting a macro to call a combo box.

--

Vasant





"Brent Walker" wrote in message
...
Experienced in Excel, but I am just learning VBA.

I want the macro to call a combo box and then select all sheets that
start
with the value in the combo. Then, it needs to copy the selected
sheets
(as
values) to a new workbook.
--
Thanks,
Brent

VBA to copy sheets (as values) that begin with the same sheet
 

Thanks. This works like a champ.

Is there any way to get it to paste the cells in the sheets as values
instead of formulas? I need to be able to email the copied workbooks and do
not want any linking errors confusing anyone.




"Vasant Nanavati" wrote:

UserForm1.Show

--

Vasant

"Brent Walker" wrote in message
...
Thanks. This is helpful.

When I said I want the macro to call a combo box, I meant that I want the
macro to call a form that contains a combo box. Any way to do that?

"Vasant Nanavati" wrote:

Assuming Sheet1 has the ComboBox:

Sub Test()
Dim ws As Worksheet, fFound As Boolean
fFound = True
For Each ws In Worksheets
If Left(ws.Name, Len(Sheet1.ComboBox1)) = Sheet1.ComboBox1 Then
ws.Select fFound
fFound = False
End If
Next
ActiveWindow.SelectedSheets.Copy
End Sub

Not sure what you mean by wanting a macro to call a combo box.

--

Vasant





"Brent Walker" wrote in message
...
Experienced in Excel, but I am just learning VBA.

I want the macro to call a combo box and then select all sheets that
start
with the value in the combo. Then, it needs to copy the selected
sheets
(as
values) to a new workbook.
--
Thanks,
Brent

VBA to copy sheets (as values) that begin with the same sheet
 

Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=False

"Brent Walker" wrote:

Thanks. This works like a champ.

Is there any way to get it to paste the cells in the sheets as values
instead of formulas? I need to be able to email the copied workbooks and do
not want any linking errors confusing anyone.




"Vasant Nanavati" wrote:

UserForm1.Show

--

Vasant

"Brent Walker" wrote in message
...
Thanks. This is helpful.

When I said I want the macro to call a combo box, I meant that I want the
macro to call a form that contains a combo box. Any way to do that?

"Vasant Nanavati" wrote:

Assuming Sheet1 has the ComboBox:

Sub Test()
Dim ws As Worksheet, fFound As Boolean
fFound = True
For Each ws In Worksheets
If Left(ws.Name, Len(Sheet1.ComboBox1)) = Sheet1.ComboBox1 Then
ws.Select fFound
fFound = False
End If
Next
ActiveWindow.SelectedSheets.Copy
End Sub

Not sure what you mean by wanting a macro to call a combo box.

--

Vasant





"Brent Walker" wrote in message
...
Experienced in Excel, but I am just learning VBA.

I want the macro to call a combo box and then select all sheets that
start
with the value in the combo. Then, it needs to copy the selected
sheets
(as
values) to a new workbook.
--
Thanks,
Brent