Hi Guys,
I am trying to copy from another workbooks based on criteria of a textbox in a userform. If I select a certain value from the textbox then it will create a new sheet with name as criteria.
If the sheet name is not exist:
- it will create/insert a sheet name, the same name with of textbox
- copy from another workbook/worksheet to this new sheet
if the sheet name is exist:
- it will not create/insert a new sheet
- copy from another workbook/worksheet to existing sheet, under existing data
The code above works, but if a sheet name is existing, it is copying to a new sheet.
How to add the code so it can identify whether a sheet is existing or not, before creating a new sheet?
Thanks so much
prima - Indonesia
I am trying to copy from another workbooks based on criteria of a textbox in a userform. If I select a certain value from the textbox then it will create a new sheet with name as criteria.
If the sheet name is not exist:
- it will create/insert a sheet name, the same name with of textbox
- copy from another workbook/worksheet to this new sheet
if the sheet name is exist:
- it will not create/insert a new sheet
- copy from another workbook/worksheet to existing sheet, under existing data
VBA Code:
Sub SystemDes_AXREP()
Application.ScreenUpdating = False
Dim thiswb As Workbook
Dim otherwb As Workbook
Dim dynaws As Worksheet
Dim LRow As Long
Set thiswb = ActiveWorkbook
Set dynaws = Sheets.Add
Workbooks.Open Filename:="\\psssvc005\P_Tangguh_Papua\12.COMMON_FOLDER\Andrea\Prima\AXREP.csv"
Set otherwb = ActiveWorkbook
LRow = Cells(Rows.Count, 6).End(xlUp).Row
Range("C3").Value = Frm_SystemDescription.SYSTEM_NUMBER.Caption
dynaws.Name = thiswb.Sheets("Menu").Range("C3").Value
Crit = thiswb.Sheets("Menu").Range("C3").Value
If dynaws.Cells(1, 1).Value = vbNullString Then
dynaws.Range("A1000000").End(xlUp).Offset(0, 0).Value = "AXREP"
dynaws.Range("A1000000").End(xlUp).Offset(0, 1).FormulaR1C1 = "=TODAY()"
Else
dynaws.Range("A1000000").End(xlUp).Offset(2, 0).Value = "AXREP"
dynaws.Range("A1000000").End(xlUp).Offset(0, 1).FormulaR1C1 = "=TODAY()"
End If
dynaws.Range("A1000000").End(xlUp).Offset(0, 1).Value = dynaws.Range("A1000000").End(xlUp).Offset(0, 1).Value
otherwb.Sheets("AXREP").Range("$A$1:$AV$1").AutoFilter Field:=3, Criteria1:="=*" & Crit & "*", Operator:=xlFilterValues
otherwb.Sheets("AXREP").Range("A1:AV" & LRow).SpecialCells(xlCellTypeVisible).Copy
dynaws.Range("A1000000").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
otherwb.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
The code above works, but if a sheet name is existing, it is copying to a new sheet.
How to add the code so it can identify whether a sheet is existing or not, before creating a new sheet?
Thanks so much
prima - Indonesia