(Solved) – Copy Dynamic Range to Another Workbook based on cell value using VBA


here am trying to copy a dynamic range(dyna) from activesheet and paste it to a new workbook located in “E:1b”, file name based on cell value(J7).
dyna = “=OFFSET(Sheet1!$D$6,0,0,COUNTA(Sheet1!$D:$J),7)”
I need help to do it.

here is code I have

Set wb = Workbooks.Add
ActiveSheet.dyna.Copy Before:=wb.Sheets(1)
Application.DisplayAlerts = False
wb.SaveAs "E:1b" & Range("J7").Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, ReadOnlyRecommended:=False, CreateBackup:=False
wb.Close "E:1b" & Range("J7").Value & ".xlsx"
Application.DisplayAlerts = True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *