I am trying to write a code that will split my current file by Column K and save as with new files
I keep getting an error at this step. Is it because my Instructions sheet is in the original file? Anyway to work around this now?
SvName = Sheets("Instructions").Range("C46")
wb.SaveAs strPath & "\" & strVal & "SvName.xlsx"
Sub CopyWorksheet()
Dim b As Integer, lastrow As Integer, lastcol As Integer
Dim wb As Workbook, wbCurr As Workbook
Dim strPath As String, strVal As String
strPath = Application.ActiveWorkbook.Path
Set wbCurr = ActiveWorkbook
lastrow = ActiveSheet.Range("K100000").End(xlUp).Row
lastcol = ActiveSheet.Range("xfd1").End(xlToLeft).Column
For K = 2 To lastrow
strVal = Range("K" & K)
Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Range(Cells(1, 1), Cells(1, lastcol)).Copy
wb.Activate
wb.ActiveSheet.Range("a1").PasteSpecial (xlPasteValues)
Dim strName As String
SvName = Sheets("Instructions").Range("C46")
wb.SaveAs strPath & "\" & strVal & "SvName.xlsx"
wbCurr.Activate
Next K
End Sub
I keep getting an error at this step. Is it because my Instructions sheet is in the original file? Anyway to work around this now?
SvName = Sheets("Instructions").Range("C46")
wb.SaveAs strPath & "\" & strVal & "SvName.xlsx"
Sub CopyWorksheet()
Dim b As Integer, lastrow As Integer, lastcol As Integer
Dim wb As Workbook, wbCurr As Workbook
Dim strPath As String, strVal As String
strPath = Application.ActiveWorkbook.Path
Set wbCurr = ActiveWorkbook
lastrow = ActiveSheet.Range("K100000").End(xlUp).Row
lastcol = ActiveSheet.Range("xfd1").End(xlToLeft).Column
For K = 2 To lastrow
strVal = Range("K" & K)
Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Range(Cells(1, 1), Cells(1, lastcol)).Copy
wb.Activate
wb.ActiveSheet.Range("a1").PasteSpecial (xlPasteValues)
Dim strName As String
SvName = Sheets("Instructions").Range("C46")
wb.SaveAs strPath & "\" & strVal & "SvName.xlsx"
wbCurr.Activate
Next K
End Sub