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

[Brent Walker]

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

[Vasant Nanavati]

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" <BrentWalker@discussions.microsoft.com> wrote in message
news:C4A9851A-1081-4E5D-A4DC-E7C1A80D4846@microsoft.com...
> 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

[Brent Walker]

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" <BrentWalker@discussions.microsoft.com> wrote in message
> news:C4A9851A-1081-4E5D-A4DC-E7C1A80D4846@microsoft.com...
> > 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
>
>
>

[Vasant Nanavati]

UserForm1.Show

--

Vasant

"Brent Walker" <BrentWalker@discussions.microsoft.com> wrote in message
news:32EFD411-1D6D-4B19-9A19-5815034B2C5D@microsoft.com...
> 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" <BrentWalker@discussions.microsoft.com> wrote in message
> > news:C4A9851A-1081-4E5D-A4DC-E7C1A80D4846@microsoft.com...
> > > 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
> >
> >
> >

[Brent Walker]

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" <BrentWalker@discussions.microsoft.com> wrote in message
> news:32EFD411-1D6D-4B19-9A19-5815034B2C5D@microsoft.com...
> > 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" <BrentWalker@discussions.microsoft.com> wrote in message
> > > news:C4A9851A-1081-4E5D-A4DC-E7C1A80D4846@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>

[Dean J.]

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" <BrentWalker@discussions.microsoft.com> wrote in message
> > news:32EFD411-1D6D-4B19-9A19-5815034B2C5D@microsoft.com...
> > > 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" <BrentWalker@discussions.microsoft.com> wrote in message
> > > > news:C4A9851A-1081-4E5D-A4DC-E7C1A80D4846@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >