Hi everyone - I am trying to re use a Macro but I am facing some issues.
Could someone help me to do so?
The aim is to print a template which values are based on another spreadsheet.
The macro should scroll from a list (which it seems I cannot change) and produce this template for each set of info.
This is the code.
Could please someone help me to understand how to understand and modify it?
For example the list in I2 how is it made? with data validation?
thanks
Sub Printing()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim Var(300, 2)
Sheets("New List").Select
LR = Cells(Rows.Count, 4).End(xlUp).Row
For a = 4 To LR
If Cells(a, 4) > 0 Then
k = k + 1
Var(k, 1) = Cells(a, 4)
End If
Next a
Sheets("Output Sheet 2").Select
For Z = 1 To k + 1
If Z < k Then
'Select the borrower
Sheets("Output Sheet 2").Select
Range("I2") = "'" & Var(k - Z, 1)
Calculate
'Rename
ActiveSheet.Copy After:=Sheets(5)
ActiveSheet.Name = "AA" & Var(k - Z, 1)
'Copy and paste values
' Cells.Select
' Selection.Copy
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
End If
Next Z
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.DisplayFormulaBar = True
.ScreenUpdating = True
End With
End Sub
Could someone help me to do so?
The aim is to print a template which values are based on another spreadsheet.
The macro should scroll from a list (which it seems I cannot change) and produce this template for each set of info.
This is the code.
Could please someone help me to understand how to understand and modify it?
For example the list in I2 how is it made? with data validation?
thanks
Sub Printing()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim Var(300, 2)
Sheets("New List").Select
LR = Cells(Rows.Count, 4).End(xlUp).Row
For a = 4 To LR
If Cells(a, 4) > 0 Then
k = k + 1
Var(k, 1) = Cells(a, 4)
End If
Next a
Sheets("Output Sheet 2").Select
For Z = 1 To k + 1
If Z < k Then
'Select the borrower
Sheets("Output Sheet 2").Select
Range("I2") = "'" & Var(k - Z, 1)
Calculate
'Rename
ActiveSheet.Copy After:=Sheets(5)
ActiveSheet.Name = "AA" & Var(k - Z, 1)
'Copy and paste values
' Cells.Select
' Selection.Copy
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
End If
Next Z
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.DisplayFormulaBar = True
.ScreenUpdating = True
End With
End Sub