the_hodgee
New Member
- Joined
- Jan 3, 2017
- Messages
- 6
Hi all,
I'm having an issue with my export button/userform. Basically, I have a file (File A) with 5 tabs on and an export button that launches a userform that lets the user click any combination of the four tabs to export to a new Excel document (File B; the Welcome! sheet is always exported). The user can name File A and it saves itself in a chosen location.
On the face of it everything works well, except when the macro finishes and File B is left on the screen, unexpected errors occur. Sometimes I cannot type in the cells of File B, sometimes I cannot click items on the ribbon in File B and every time I click print preview in File B it shows me the sheet with the export button on from File A! To solve this I have to click on different tabs in File B and this sometimes activates the workbook, and sometimes I even have click on a different workbook and then back into File B in order for all the functionality and features to start working again like they should.
Could anyone take a look at my code below and see if they could see anything that is potentially making it unstable/work incorrectly? (I've tried to annotate it as well as I can to highlight the different stages). As you can see I try activating File B at the end of the macro and I've tried many variants of this but every time I export, File B is practically uneditable until I click off the workbook and back in it again.
Many thanks in advance!
I'm having an issue with my export button/userform. Basically, I have a file (File A) with 5 tabs on and an export button that launches a userform that lets the user click any combination of the four tabs to export to a new Excel document (File B; the Welcome! sheet is always exported). The user can name File A and it saves itself in a chosen location.
On the face of it everything works well, except when the macro finishes and File B is left on the screen, unexpected errors occur. Sometimes I cannot type in the cells of File B, sometimes I cannot click items on the ribbon in File B and every time I click print preview in File B it shows me the sheet with the export button on from File A! To solve this I have to click on different tabs in File B and this sometimes activates the workbook, and sometimes I even have click on a different workbook and then back into File B in order for all the functionality and features to start working again like they should.
Could anyone take a look at my code below and see if they could see anything that is potentially making it unstable/work incorrectly? (I've tried to annotate it as well as I can to highlight the different stages). As you can see I try activating File B at the end of the macro and I've tried many variants of this but every time I export, File B is practically uneditable until I click off the workbook and back in it again.
Many thanks in advance!
Code:
Private Sub Ok_Click()
'Turning off screen updating to speed up the code
Application.ScreenUpdating = False
'Setting the dimensions for this macro
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, nws1 As Worksheet
Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("Products")
Set ws2 = wb1.Sheets("Customer")
Set ws3 = wb1.Sheets("Sandbox Pivot Table 1")
Set ws4 = wb1.Sheets("Sandbox Pivot Table 2")
Set ws5 = wb1.Sheets("Welcome!")
'If any of the checkboxes have been ticked on the userform, add a new workbook
If CheckBox1 = True Or CheckBox2 = True Or CheckBox3 = True Or CheckBox4 = True Then
Set wb2 = Workbooks.Add
'Establishing the name of the file (which will be used as the save name at the end) and the save location
Dim fname, fpath
fname = InputBox("Please name your new report!")
fpath = "[File Location]" & fname & ".xlsx"
'Exporting the "Welcome!" sheet
If CheckBox1 = True Or CheckBox2 = True Or CheckBox3 = True Or CheckBox4 = True Then
With wb1.Worksheets("Welcome!").Cells.Copy
With wb2
Set nws1 = .Worksheets("Sheet1")
nws1.Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
ActiveSheet.Name = "Overview"
With ActiveSheet.Cells(1, 11)
.Value = "Analysis for " & fname
.Font.Size = 25
.Font.Bold = True
End With
With ActiveSheet.Cells(25, 11)
.Value = "With:"
.Font.Size = 22
.Font.Bold = True
End With
Range("J6:J7,J20").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
With Range("J6:M7,P6:R7,U6:W7,J20:M20,P20:R20,U20:W20")
.Font.Bold = True
End With
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End With
End If
'Exporting the "Products" sheet
If CheckBox1 = True Then
With wb1.Worksheets("Products").Cells.Copy
With wb2
Sheets.Add After:=Sheets(.Sheets.Count)
ActiveSheet.Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
ActiveSheet.Name = "Products"
With ActiveSheet.Cells(1, 1)
.Value = "Products"
.Font.Size = 16
.Font.Bold = True
.Font.Underline = True
End With
Range("A3:C5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
With Range("A3:I5")
.Font.Bold = True
End With
Range("A5:C5").Copy
Cells(Rows.Count, 1).End(xlUp).Select
With Selection
.PasteSpecial (xlPasteFormats)
End With
Cells(Rows.Count, 1).End(xlUp).EntireRow.Select
With Selection
.Font.Bold = True
End With
Rows(3).EntireRow.Delete
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Columns("A:C").AutoFit
Cells(1, 1).Select
End With
End If
'Exporting the "Customer" sheet
If CheckBox2 = True Then
With wb1.Worksheets("Customer").Cells.Copy
With wb2
Sheets.Add After:=Sheets(.Sheets.Count)
ActiveSheet.Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
With ActiveSheet.Cells(1, 1)
.Value = "Customers"
.Font.Size = 16
.Font.Bold = True
.Font.Underline = True
End With
ActiveSheet.Name = "Customer"
Range("A3:C5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
With Range("A3:G5")
.Font.Bold = True
End With
Range("A5:C5").Copy
Cells(Rows.Count, 1).End(xlUp).Select
With Selection
.PasteSpecial (xlPasteFormats)
End With
Cells(Rows.Count, 1).End(xlUp).EntireRow.Select
With Selection
.Font.Bold = True
End With
Rows(3).EntireRow.Delete
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Columns("A:C").AutoFit
Cells(1, 1).Select
End With
End If
'Exporting the "Sandbox Pivot Table 1" sheet
If CheckBox3 = True Then
With wb1.Worksheets("Sandbox Pivot Table 1").Cells.Copy
With wb2
Sheets.Add After:=Sheets(.Sheets.Count)
ActiveSheet.Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
ActiveSheet.Name = "Sandbox Pivot Table 1"
End With
With Range("A3:I5")
.Font.Bold = True
End With
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Columns("A:C").AutoFit
Cells(1, 1).Select
End If
'Exporting the "Sandbox Pivot Table 2" sheet
If CheckBox4 = True Then
With wb1.Worksheets("Sandbox Pivot Table 2").Cells.Copy
With wb2
Sheets.Add After:=Sheets(.Sheets.Count)
ActiveSheet.Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
ActiveSheet.Name = "Sandbox Pivot Table 2"
End With
With Range("A3:I5")
.Font.Bold = True
End With
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Columns("A:C").AutoFit
Cells(1, 1).Select
End If
'Save the workbook as the name chosen earlier and select the workbook
ActiveWorkbook.SaveAs fpath
'If the checkboxes are false...
Me.CheckBox1 = False
Me.CheckBox2 = False
Me.CheckBox3 = False
Me.CheckBox4 = False
Unload UserForm1
'Turn screen updating back on
Application.ScreenUpdating = True
End If
Windows(fname).Activate
Workbooks(fname).Activate
Sheets("Overview").Select
Cells(1, 1).Select
End Sub
