Excel VBA import range

Question

Hi

I'm looking for a way to import data range (paste special values) into new workbook

I need it done 3 times

R10 to AZ15 all three times

I generally used manually copy paste or used get data but it seems to be taking long time. I'm looking for a better way to import data range

Answer 1

Hi,

try this

save 3 new workbooks in active workbook path

Sub Loop_Create_New_Files()

'25-03-2025

Set wb1 = ThisWorkbook

Set ws = ActiveSheet

Application.ScreenUpdating = False

For x = 1 To 3 ' << create 3 new wbs book1/2/3. xlsx

Set wb2 = Workbooks.Add(1)

Application.DisplayAlerts = False

wb2.SaveAs Filename:=wb1.Path & "\book" & x & ".xlsx"

Application.DisplayAlerts = True

ws.Range**("R10:AZ15")**.Copy

wb2.Sheets(1).Cells(1, 1).PasteSpecial xlFormats

wb2.Sheets(1).Cells(1, 1).PasteSpecial xlValues

Application.CutCopyMode = False

wb2.Sheets(1).UsedRange.EntireColumn.AutoFit

wb2.Save

wb2.Close False

Next x

Application.ScreenUpdating = True

MsgBox "done"

End Sub