I want to create an entirely new workbook from my existing file with a robust list.
I've managed to create a new workbook with its sheets, but I really want to generate the custom workbook name based on the other cell in the row, where my cell was selected.
Like you see above. In the AD column, every row has the cell with GENERATE value, which runs the macro. When I click for instance on cell AD5 I want my workbook to be saved under the name fetched from cell D5. Unfortunately, the code below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim answer As Integer
Dim lrou As Long
Dim i As Long
Dim c As Range
lrou = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
If Selection.Count = 1 Then
If Not Intersect(Target, Range("AD3:AD" & lrou)) Is Nothing Then
For Each c In Range("AD3:AD" & lrou)
If LCase(c.Value) <> "Complete" Then
answer = MsgBox("Do you want to create the Pre-Survey form?", vbQuestion + vbYesNo + vbDefaultButton2, "CST Tracker")
If answer = vbYes Then
Call Pre_Surveyform
Else
Exit Sub
End If
End If
Next c
End If
End If
End Sub
MACRO IN MODULE 1
Sub Pre_Surveyform()
Dim wkb As Workbook
Dim rng As Range
Dim Lr As Long
Dim n As Long
Lr = Cells(Rows.Count, "B").End(xlUp).Row
Dim fwsk As Worksheet, cnws As Worksheet, orwsk As Worksheet, hosws As Worksheet
For n = 1 To Lr
Set rng = Cells(n, "D")
Next n
Set wkb = Workbooks.Add
With wkb
'.SaveAs "NewXcel", FileFormat:=xlOpenXMLWorkbookMacroEnabled
.SaveAs Filename:=rng, FileFormat:=xlOpenXMLWorkbookMacroEnabled
.Sheets("Sheet1").Name = "Frontsheet"
Set fwsk = .Sheets("Frontsheet")
Set cnws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
With cnws
.Name = "Client Network Plan"
End With
Set orwsk = .Sheets.Add(After:=.Sheets(.Sheets.Count))
With orwsk
.Name = "OR Portal Image"
End With
Set hosws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
With hosws
hosws.Name = "Hospital & Welfare"
End With
End With
End Sub
I receive just the value from the last column. In other cases, I get the 1004 error.
Is there any chance to make my selection synchronized with the row, where the cell is clicked?
I found some solutions here:
https://www.exceldemy.com/select-range-based-on-cell-value-vba/
from where the following code comes.
UPDATE:
I found some nice hints here: How to use the Target parameter in a standard module?
And adjusted my macro as per both this hint and the answer below:
Dim wkb As Workbook
Dim Lr As Long
Dim n As Long
Dim Saverng As Range
Lr = Cells(Rows.Count, "B").End(xlUp).Row
Dim cstws As Worksheet, cpws As Worksheet
Dim fwsk As Worksheet, cnws As Worksheet, orwsk As Worksheet, hosws As
Worksheet
Dim shp As Shape
Dim Target As Range
Dim SelectedRow As Integer
Set Target = ActiveCell
SelectedRow = Target.Row
Set cstws = ThisWorkbook.Sheets("CST Tracker")
Set cpws = ThisWorkbook.Sheets("Control Page")
Set shp = ThisWorkbook.Sheets("Control Page").Shapes("Se")
Saverng = cstws.Range("K" & SelectedRow).Value
MsgBox Target.Address & vbNewLine & Target.Row & vbNewLine & Target.Column & vbNewLine & Saverng
At the following line: Saverng = cstws.Range("K" & SelectedRow).Value
I keep getting Object variable or With block variable not set
Where is the problem now?
