macro to create copy of template based on two criteria and paste data from master sheet

Hi, deepsi1,

you should clarify if you want to copy the data into the Columns A to D of the new sheets or into the yellow fields (if so then why are there 2 for Name in the Gsec sheets?).

Ciao,
Holger

Hi, deepsi1,

give this a try:

Option Explicit

Sub EF920361()
Dim wsMaster As Worksheet
Dim wsCopy As Worksheet
Dim lngLast As Long
Dim lngCounter As Long
Dim lngArr As Long
Dim blnPlus As Boolean
Dim varArr1
Dim varArr2
Dim varArr

varArr1 = Array("B2", "B4", "B7", "C7")
varArr2 = Array("B2", "B4", "B8", "C8")

Set wsMaster = Sheets("Sheet1")
lngLast = wsMaster.Cells(Rows.Count, "E").End(xlUp).Row

For lngCounter = 2 To lngLast
  Select Case wsMaster.Cells(lngCounter, "E").Value
    Case "GSE", "GGB", "ZCG", "TBL"
      blnPlus = True
      If wsMaster.Cells(lngCounter, "F").Value = "P" Then
         Sheets("Gsec Pur").Copy After:=Worksheets(Worksheets.Count)
         Set wsCopy = ActiveSheet
      Else
        Sheets("Gsec Sale").Copy After:=Worksheets(Worksheets.Count)
        Set wsCopy = ActiveSheet
      End If
    Case Else
      If wsMaster.Cells(lngCounter, "F").Value = "P" Then
         Sheets("Buy").Copy After:=Worksheets(Worksheets.Count)
         Set wsCopy = ActiveSheet
      Else
        Sheets("Sale").Copy After:=Worksheets(Worksheets.Count)
        Set wsCopy = ActiveSheet
      End If
  End Select
  
  wsCopy.Name = wsMaster.Cells(lngCounter, "A").Value
  If blnPlus Then
    varArr = varArr2
  Else
    varArr = varArr1
  End If
  
  For lngArr = LBound(varArr) To UBound(varArr)
    wsCopy.Range(varArr(lngArr)).Value = wsMaster.Cells(lngCounter, lngArr + 1).Value
  Next lngArr
  
  Erase varArr
Next lngCounter

Set wsCopy = Nothing
Set wsMaster = Nothing

End Sub

Ciao,
Holger

Hi, deepsi1,

please alter the code like this:

For lngCounter = 2 To lngLast
  blnPlus = False
  Select Case wsMaster.Cells(lngCounter, "E").Value

Ciao,
Holger

Hi, deepsi1,

either place a boolean value next to the data already worked on or use either a loop or try to activate the sheet to find out what action needs to be taken (skip or add a new one). IŽd go with the additional information in the original Master.

Ciao,
Holger

Hi, deepsi1,

add the cells to copy and paste in the Arrays:

varArr1 = Array("B2", "B4", "B7", "C7", "F7", "G7")
varArr2 = Array("B2", "B4", "B8", "C8", "F8", "G8")

The copying will be done by the length of the Arrays but the cells would need to be directly side by side (no other data in the columns from A to F). If you want other values in there you would need another array holding the columns from which to take and in which order.

Ciao,
Holger

Hi, deepsi,

maybe use another Boolean for the check of whether the sheet already exists or needs to be copied. Code is untested:

Sub EF920361_2()
Dim wsMaster As Worksheet
Dim wsCopy As Worksheet
Dim lngLast As Long
Dim lngCounter As Long
Dim lngArr As Long
Dim blnPlus As Boolean
Dim blnNewSh As Boolean
Dim varArr1
Dim varArr2
Dim varArr

varArr1 = Array("B2", "B4", "B7", "C7", "F7", "G7")
varArr2 = Array("B2", "B4", "B8", "C8", "F8", "G8")

Set wsMaster = Sheets("Sheet1")
lngLast = wsMaster.Cells(Rows.Count, "E").End(xlUp).Row

For lngCounter = 2 To lngLast
  blnPlus = False
  Select Case wsMaster.Cells(lngCounter, "E").Value
    Case "GSE", "GGB", "ZCG", "TBL"
      blnPlus = True
      If NewSheet(wsMaster.Cells(lngCounter, "A").Value) Then
        If wsMaster.Cells(lngCounter, "F").Value = "P" Then
           Sheets("Gsec Pur").Copy After:=Worksheets(Worksheets.Count)
           Set wsCopy = ActiveSheet
        Else
          Sheets("Gsec Sale").Copy After:=Worksheets(Worksheets.Count)
          Set wsCopy = ActiveSheet
        End If
      Else
        Set wsCopy = Sheets(wsMaster.Cells(lngCounter, "A").Value)
      End If
    Case Else
      If NewSheet(wsMaster.Cells(lngCounter, "A").Value) Then
        If wsMaster.Cells(lngCounter, "F").Value = "P" Then
           Sheets("Buy").Copy After:=Worksheets(Worksheets.Count)
           Set wsCopy = ActiveSheet
        Else
          Sheets("Sale").Copy After:=Worksheets(Worksheets.Count)
          Set wsCopy = ActiveSheet
        End If
      Else
        Set wsCopy = Sheets(wsMaster.Cells(lngCounter, "A").Value)
      End If
  End Select
  
  wsCopy.Name = wsMaster.Cells(lngCounter, "A").Value
  If blnPlus Then
    varArr = varArr2
  Else
    varArr = varArr1
  End If
  
  For lngArr = LBound(varArr) To UBound(varArr)
    wsCopy.Range(varArr(lngArr)).Value = wsMaster.Cells(lngCounter, lngArr + 1).Value
  Next lngArr
  
  Erase varArr
Next lngCounter

Set wsCopy = Nothing
Set wsMaster = Nothing

End Sub

Private Function NewSheet(strShtName As String) As Boolean
On Error Resume Next
Sheets(strShtName).Select
NewSheet = Err <> 0
End Function

Evaluate could be used to check the existence of the sheet instead of activating the sheet like shown in the Function above.

Ciao,
Holger