excel - VBA, importing large data from multiple workbooks into mastersheet -
my code opens file picker , selects files , particular column im interested in combining master worksheet.
i pick several .csv files , bring in column of choosing .
issue have are,
1) these files large, 400kb.
2) run time error 1004, copy area , paste area not same size , shape. running out of space on excel sheet? when debug error on line copyrng.copy destrng
my end goal see , count , see unique values col c(perhaps other columns) workbooks.
option explicit dim wsmaster workbook, csvfiles workbook dim filename string dim file integer dim r long public sub consolidate() application .screenupdating = false .enableevents = false end application.filedialog(msofiledialogopen) .allowmultiselect = true .title = "select files process" .show if .selecteditems.count = 0 exit sub set wsmaster = activeworkbook dim copyrng range, destrng range dim firstrow long file = 1 .selecteditems.count filename = .selecteditems.item(file) if right(filename, 4) = ".csv" set csvfiles = workbooks.open(filename, 0, true) r = wsmaster.sheets("sheet1").range("c" & rows.count).end(xlup).row '' main new part set copyrng = csvfiles.sheets(1).range("c1:c" & r) wsmaster.sheets("sheet1") firstrow = .cells(.rows.count, 2).end(xlup).row set destrng = .range("a" & firstrow + 1).offset(0, 1) end copyrng.copy destrng '''''''''' csvfiles.close savechanges:=false 'close without saving end if next file end set wsmaster = nothing set csvfiles = nothing application .screenupdating = true .enableevents = true end end sub
updated code below recommendation
option explicit dim wsmaster workbook, csvfiles workbook dim filename string dim file integer dim r long public sub consolidate() application .screenupdating = false .enableevents = false end application.filedialog(msofiledialogopen) .allowmultiselect = true .title = "select files process" .show if .selecteditems.count = 0 exit sub set wsmaster = activeworkbook dim copyrng range, destrng range dim firstrow long file = 1 .selecteditems.count filename = .selecteditems.item(file) if right(filename, 4) = ".csv" set csvfiles = workbooks.open(filename, 0, true) r = csvfiles.sheets(1).range("c" & rows.count).end(xlup).row '' main new part set copyrng = csvfiles.sheets(1).range("c1:c" & r) wsmaster.sheets("sheet1") firstrow = .cells(.rows.count, 2).end(xlup).row + 1 set destrng = .range("b" & firstrow & "b" & (firstrow + r)) end destrng.value = copyrng.value '''''''''' csvfiles.close savechanges:=false 'close without saving end if next file end set wsmaster = nothing set csvfiles = nothing application .screenupdating = true .enableevents = true end end sub
since number of rows defined r
can set dimensions of destination range. change below should fix copy-paste error , speed code eliminating use of clipboard (assuming want values copied).
if right(filename, 4) = ".csv" set csvfiles = workbooks.open(filename, 0, true) r = csvfiles.sheets(1).range("c" & rows.count).end(xlup).row '' main new part set copyrng = csvfiles.sheets(1).range("c1:c" & r) wsmaster.sheets("sheet1") firstrow = .cells(.rows.count, 2).end(xlup).row + 1 set destrng = .range("b" & firstrow & ":b" & (firstrow + r)) end destrng.value = copyrng.value '''''''''' csvfiles.close savechanges:=false 'close without saving end if
Comments
Post a Comment