vba copy range to new workbook

[hajiali]

Hello all,

Im needing a macro when pressed to Copy range("T:AD") to new sheet in "A1" and to copy the sheet the amount of time as the number of days in the month that's in "AD1"

Ex. if AD1=12/1/2020 then to copy the sheet 31 times as there are 31 days in December.
with each New sheet to change the value in "K1" to next day. and rename the sheet to value of "K1" with the "DD" format.

any help is greatly appreciate.

 

[JLGWhiz]

See if you can use this:

Sub t()
Dim ct As Long, sh As Worksheet, dt As Date
With ActiveSheet
Set sh = Sheets(.Range("A1").Value)
ct = Day(Application.EoMonth(Range("AD1"), 0))
dt = .Range("K1").Value
    For i = 1 To ct
        dt = dt + 1
        Sheets.Add After:=Sheets(Sheets.Count)
        With sh
            Intersect(.UsedRange, .Columns("T:AD")).Copy ActiveSheet.Range("A1")
        End With
        Sheets(Sheets.Count).Name = Format(dt, "DD-MM-YYYY") 'Not sure what you want here.
    Next
End With
End Sub

 

[hajiali]

Im getting a error on the following line

Set sh = Sheets(.Range("A1").Value)

Error is "run-time error '9': Subscript out of range

 

[JLGWhiz]

I think I got confused about which sheet you want to copy from and which you want to paste to. Try this.

Sub t()
Dim ct As Long, sh As Worksheet, dt As Date
Set sh = ActiveSheet
ct = Day(Application.EoMonth(sh.Range("AD1"), 0))
dt = sh.Range("K1").Value
    For i = 1 To ct
        dt = dt + 1
        Sheets.Add After:=Sheets(Sheets.Count)
        With sh
            Intersect(.UsedRange, .Columns("T:AD")).Copy ActiveSheet.Range("A1")
        End With
        Sheets(Sheets.Count).Name = Format(dt, "DD-MM-YYYY") 'Not sure what you want here.
    Next
End Sub

 

[hajiali]

That fixed that error. few more fixes. the value of each new sheet in K1 is 12/1/2020 and does not add the 1 day to each new sheet. I would also like the function to auto column width if to each new sheet.

 

[hajiali]

I was able to get the auto width function added to the code just can not seem to get the Add 1 day to each new sheet.

 

[JLGWhiz]

This adds the Autofit for columns A:K. My test setup indicates that the day is being added to the value in K1.. It produces sheet names in the format "DD-MM--YYYY". If you want something different you can modify the line with the comment on it.

Sub t()
Dim ct As Long, sh As Worksheet, dt As Date
Set sh = ActiveSheet
ct = Day(Application.EoMonth(sh.Range("AD1"), 0))
dt = sh.Range("K1").Value
    For i = 1 To ct
        dt = dt + 1
        Sheets.Add After:=Sheets(Sheets.Count)
        With sh
            Intersect(.UsedRange, .Columns("T:AD")).Copy ActiveSheet.Range("A1")
        End With
        Sheets(Sheets.Count).Name = Format(dt, "DD-MM-YYYY") 'Not sure what you want here.
        Sheets(Sheets.Count).Coulumns("A:K").AutoFit
    Next
End Sub

 

[hajiali]

Sub ExportEL()
Dim ct As Long, sh As Worksheet, dt As Date
Set sh = ActiveSheet
ct = Day(Application.EoMonth(sh.Range("AD1"), 0))
dt = sh.Range("K1").Value
    For i = 1 To ct
        dt = dt + 1
        Sheets.Add After:=Sheets(Sheets.Count)
        With sh
            Intersect(.UsedRange, .Columns("T:AH")).Copy ActiveSheet.Range("A1")
        End With
        Sheets(Sheets.Count).Name = Format(dt, "DD")
            Sheets("Ramp SUP").Columns("T:AD").Copy
            Range("A1").Select
           Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        ActiveSheet.Columns("L:O").Hidden = True
    Next
End Sub

See above adjustments that I have made. AD1 still has the date 12/1/2020 when T:AH is copied to new sheet in A1 the date is in K1 on each new sheets however with my model it does not add the day to K1 of each sheet. It does name each sheet as 01,02,03,... etc up to the end of the month however in all sheets "02" to all last sheet "31" value in K1 is 12/1/2020. The value of K1 in sheet "02" should be 12/2/2020, Value in K1 in sheet "03" should be 12/3/2020..etc.

 

[JLGWhiz]

Is the value in K1 a date data type or is it string data type.? If it is date data type, the code should be adding a day to each new sheet name. If it is formatted as text then it might not add the day.. But in my test, it definitely added a day to each sheet because the value in K1 was a date data type.

 

[hajiali]

New Exception Log Layout.xlsm
	AD
1	12/1/2020
Ramp SUP

here is the format of that cell

Should it be something different?