CODEWITHSUNDEEP
vbaexcel
Ashok
Ashok

Reputation: 394

Create workbooks in loop

hi i am working on filtering the data and copy in to new workbook. i cant renaming as mayday(adding integer value in the string) in the new workbook and getting type mismatch error- pls help what need to do

Sub ButtoClick()
Dim wbs2 As Worksheet
Dim wkb As Workbook
Dim strpath As String
Set wbs2 = Workbooks("Nov Collections-CF").Worksheets("Nov Collections-CF")
 Dim myday As Integer

     strpath = ("C:\Users\ashokkumar.d\Desktop\New folder (2)\")
    ran = ThisWorkbook.ActiveSheet.Range("A1:A16")

        For Each cel In ran
            myday = Day(cel)
            dDate = DateSerial(Year(cel), Month(cel), Day(cel))
            Set wkb = Workbooks.Add
            wkb.SaveAs FileName:=(strpath + "Nov Collections-CF" + myday + ".xlsx") 'Type Mismatch Error
            wbs2.Range("A1:K1").AutoFilter Field:=4, Criteria1:=Format(dDate, "dd/mm/yyyy"), Operator:=xlFilterValues

            wbs2.UsedRange.SpecialCells(xlCellTypeVisible).Copy
             With wkb.Sheets(1).Range("A1")
                    .PasteSpecial xlPasteFormats
                    .PasteSpecial xlPasteValues
                End With

            wkb.Save
            wkb.Close
        Next

End Sub

Upvotes: 1

Views: 545

Answers (3)

Vityata
Vityata

Reputation: 43595

If you want to concatenate with a +, you should make sure that all the values are strings. myday is declared as an integer. Thus, try to "stringify" it:

wkb.SaveAs Filename:=(strpath + "Nov Collections-CF" + CStr(myday) + ".xlsx")

Upvotes: 0

Shirayuki
Shirayuki

Reputation: 189

VBA probably got confused when you are trying to add numbers and strings together using "+"

Change

wkb.SaveAs FileName:=(strpath + "Nov Collections-CF" + myday + ".xlsx")

to this instead

wkb.SaveAs FileName:=(strpath & "Nov Collections-CF" & myday & ".xlsx")

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

Try to concatenate with ampersands, like this:

 wkb.SaveAs FileName:=(strpath & "Nov Collections-CF" & myday & ".xlsx")

Additionally you could add conversion function:

 wkb.SaveAs FileName:=(strpath & "Nov Collections-CF" & Cstr(myday) & ".xlsx")

Upvotes: 1

Related Questions