Excel help please, Button to export data

[Neil Holden]

Below is the code to export the data from 3 worksheets to an external sheet.

This works perfect, however i am going to be needing to alter this code on a
weekly basis as sheets are constantly being added.

The question i have is, can i make my own excel worksheet and allow the user
to type in the new name of the worksheet to export?

For example:

column A
EAM676
EAM788
EAM872
EAM338

When the button is pressed the data is exported to the external sheet?

Would love an answer to this and would be much appreciated.

Neil.

Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, lngRow As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business Objects\CHR\Export of
SGUK.xls")
Set ws = wb2.Sheets("Sheet1")

lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
wb1.Sheets("EAM605").Range("A8:T27").Copy ws.Range("A" & lngRow)
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
wb1.Sheets("EGC613").Range("A8:T27").Copy ws.Range("A" & lngRow)
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
wb1.Sheets("ECP621").Range("A8:T27").Copy ws.Range("A" & lngRow)

wb2.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Jim Thomlinson]

One way...
Add a list box and command button from the control toolbox. Right click the
list box and select properties. Change the MultiSelect property to
fmMultiSelect.

Add this code to the worksheet

'**************************************
Private Sub CommandButton1_Click()
Dim lng As Long
Dim col As Collection

Dim wb2 As Workbook
Dim ws As Worksheet

Set col = New Collection
For lng = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(lng) Then
col.Add Me.ListBox1.List(lng)
End If
Next lng

If col.Count = 0 Then
MsgBox "No Selections"
Else
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business Objects\CHR\Export of
SGUK.xls ")
Set ws = wb2.Sheets("Sheet1")

For Each wks In col
wks.Range("A8:T27").Copy ws.Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
Next wks
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub

Private Sub Worksheet_Activate()
Dim wks As Worksheet

ListBox1.Clear
For Each wks In Worksheets
If wks.CodeName <> Me.CodeName Then _
ListBox1.AddItem wks.Name
Next wks
End Sub
'**************************************

This is still a little rough but it should give you some ideas. The list box
should be populated on workbook open and there should be a clear button for
the selected sheets and ... but this is a start.

 

[Neil Holden]

Thanks for your help Jim, I have done what you said but i'm getting an error
on the following:

For lng = 0 To Me.ListBox1.ListCount - 1

 

[Jim Thomlinson]

The default name for a list box is ListBox1. I just wanted to confirm that
you added a list box and that you did not rename it and that the list box
came from teh control toolbox and not the forms toolbar.

 

[Jim Thomlinson]

When you go to another sheet and then back to this one does the listbox
populate? It should have the names of all of the worksheets in it.

 

[Neil Holden]

Hi Jim, thanks for getting back to me.

I can confirm I have created a list box from the control toolbook.

The error says Complie Error: Syntax error and highlights

For lng = 0 Me.ListBox1.listcount - 1 (i think its the 'me' part that is the
causing the error.

Thanks Jim.

 

[Jim Thomlinson]

The code that I posted needs to be placed in the sheet and not in a code
module. In XL right click the sheet tab and select view code. That is where
the code needs to be. In code Me refers to the parent object. If used in a
sheet it refers to the sheet. In a general module there is no parent object
so it will crash.