I have a userform which creates a new file and then pastes data into that file from the current file.
I'm having an issue though, in that the ribbon doesn't respond in the new file until I go into the current file and click around. I tried using CutCopyMode = False at the end of the userform routine, but it didn't work. I'm new to VBA and not sure what else to try.
Here's the code for the userform
<code>
Private Sub ExportSubmit_Click()
' Set sheets to copy from
Dim wsSupp As Worksheet
Dim wsPQE As Worksheet
Dim wsControls As Worksheet
Set wsSupp = Worksheets("Supplier_DL")
Set wsPQE = Worksheets("PQE_DL")
Set wsControls = Worksheets("Help & Controls")
' Open new workbook from template
Sheets("Help & Controls").Activate
Sheets("Help & Controls").Range("F3").Select
Dim templatePath As String: templatePath = ActiveCell.Value
Sheets("Help & Controls").Range("F4").Select
Dim trackerPath As String: trackerPath = ActiveCell.Value
Dim strTemplate As String: strTemplate = "QA_Tracker_Template.xltm"
Dim templateFile As String: templateFile = templatePath & strTemplate
Dim wb As Workbook
Set wb = Workbooks.Add(templateFile)
' Name and Save new workbook
Dim wbname As String: wbname = "QAL-" & Me.QALNumber.Value & " Distribution & Tracker.xlsm"
wb.SaveAs Filename:=trackerPath & wbname, FileFormat:=52
ThisWorkbook.Activate
Dim NumRows As Integer, NumCols As Integer
With wsSupp
NumRows = Sheets("Supplier_DL").UsedRange.Rows.Count
NumCols = Sheets("Supplier_DL").UsedRange.Columns.Count
End With
'Copy data to new file
ThisWorkbook.Worksheets("Supplier_DL").Range("A2:F" & NumRows).Copy Destination:= _
Workbooks(wbname).Worksheets("Distribution List Check").Range("B2:G" & NumRows)
Workbooks(wbname).Activate
Sheets("Distribution List Check").Rows("1:2").EntireRow.Insert
Sheets("Distribution List Check").Range("A1").Value = "QAL-" & Me.QALNumber.Value & ":"
Sheets("Distribution List Check").Range("B1").Value = Me.QALTitle.Value
Sheets("Distribution List Check").Range("A2").Value = "Due Date:"
Sheets("Distribution List Check").Range("B2").Value = Me.monthDue.Value & " " & Me.dayDue.Value & ", " & Me.yearDue.Value
Range("A1:B2").Font.ColorIndex = 0
Range("A1:B2").Font.Bold = True
Range("A1:A2").HorizontalAlignment = xlRight
Range("B1:B2").HorizontalAlignment = xlLeft
Range("1:2").EntireRow.Interior.Color = RGB(205, 205, 205)
Dim cell As Range
For Each cell In Range("A1:H2")
With cell.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Next cell
'Clear input controls.
Me.QALNumber.Value = ""
Me.QALTitle.Value = ""
Me.monthDue.Value = ""
Me.dayDue.Value = ""
Me.yearDue.Value = ""
Unload Me
End Sub
</code>
I'm having an issue though, in that the ribbon doesn't respond in the new file until I go into the current file and click around. I tried using CutCopyMode = False at the end of the userform routine, but it didn't work. I'm new to VBA and not sure what else to try.
Here's the code for the userform
<code>
Private Sub ExportSubmit_Click()
' Set sheets to copy from
Dim wsSupp As Worksheet
Dim wsPQE As Worksheet
Dim wsControls As Worksheet
Set wsSupp = Worksheets("Supplier_DL")
Set wsPQE = Worksheets("PQE_DL")
Set wsControls = Worksheets("Help & Controls")
' Open new workbook from template
Sheets("Help & Controls").Activate
Sheets("Help & Controls").Range("F3").Select
Dim templatePath As String: templatePath = ActiveCell.Value
Sheets("Help & Controls").Range("F4").Select
Dim trackerPath As String: trackerPath = ActiveCell.Value
Dim strTemplate As String: strTemplate = "QA_Tracker_Template.xltm"
Dim templateFile As String: templateFile = templatePath & strTemplate
Dim wb As Workbook
Set wb = Workbooks.Add(templateFile)
' Name and Save new workbook
Dim wbname As String: wbname = "QAL-" & Me.QALNumber.Value & " Distribution & Tracker.xlsm"
wb.SaveAs Filename:=trackerPath & wbname, FileFormat:=52
ThisWorkbook.Activate
Dim NumRows As Integer, NumCols As Integer
With wsSupp
NumRows = Sheets("Supplier_DL").UsedRange.Rows.Count
NumCols = Sheets("Supplier_DL").UsedRange.Columns.Count
End With
'Copy data to new file
ThisWorkbook.Worksheets("Supplier_DL").Range("A2:F" & NumRows).Copy Destination:= _
Workbooks(wbname).Worksheets("Distribution List Check").Range("B2:G" & NumRows)
Workbooks(wbname).Activate
Sheets("Distribution List Check").Rows("1:2").EntireRow.Insert
Sheets("Distribution List Check").Range("A1").Value = "QAL-" & Me.QALNumber.Value & ":"
Sheets("Distribution List Check").Range("B1").Value = Me.QALTitle.Value
Sheets("Distribution List Check").Range("A2").Value = "Due Date:"
Sheets("Distribution List Check").Range("B2").Value = Me.monthDue.Value & " " & Me.dayDue.Value & ", " & Me.yearDue.Value
Range("A1:B2").Font.ColorIndex = 0
Range("A1:B2").Font.Bold = True
Range("A1:A2").HorizontalAlignment = xlRight
Range("B1:B2").HorizontalAlignment = xlLeft
Range("1:2").EntireRow.Interior.Color = RGB(205, 205, 205)
Dim cell As Range
For Each cell In Range("A1:H2")
With cell.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Next cell
'Clear input controls.
Me.QALNumber.Value = ""
Me.QALTitle.Value = ""
Me.monthDue.Value = ""
Me.dayDue.Value = ""
Me.yearDue.Value = ""
Unload Me
End Sub
</code>