Copying spreadsheets in directory into master spreadsheet

  • Thread starter Thread starter dtguitarfan
  • Start date Start date
D

dtguitarfan

Hello everyone,
I am trying to make some VBA code which will copy all the spreadsheets
within a specified directory into one master spreadsheet. I am new at
this, and have cannibalized some code from this site to do this. The
problem I am having is with changing the row heights and column widths
to match the spreadsheets I am getting the data from. I have two
formulas, CopyColumnWidths and CopyRowHeigths, set up like so:

Private Sub CopyRowHeigths(TargetRange As Range, SourceRange As Range)

I am trying to pass in the range of all the used cells from the first
spreadsheet in the directory I am getting data from as the first
parameter. To get the second parameter, I have a formula that finds
the last row with data in it. I use this formula on the master sheet
before copying data from the first sheet in the directory and as
StartRow as the result. Then I copy the data. Then I use this formula
again and cas EndRow as the result. Now I am trying to set up a range
that I can pas into CopyRowHeigths and CopyRowWidths. I am not sure
how to do this. One idea I had was to make a string and then convert
it into a range. Here is what I have:

DestRangeStr = "DestSh.Range(\""" + StartRow + "1:" + EndRow + "1" +
"\""" + ")"
DestRange = DestRangeStr
CopyColumnWidths(sh.UsedRange, DestRange)
CopyRowHeigths(sh.UsedRange, DestRange)

I am not sure if I am using the code correctly to put the quotation
mark into the string. I thought if I used the escape key, \, and then
a quotation mark it would put that within the string. The compiler
automatically put a third quotation mark in for me so it looked like
this: \""". I'm not sure if this is right. Does anyone know what I'm
doing wrong or have a better way to do it?
 
If you copy the complete row, then the rowheight will change when you paste it.

dim DestRng as range
dim SourceRng as range

with SomeWorkbook.worksheets("sheet1")
set sourcerng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

with otherworkbook.worksheets("sheet1")
set destrng = 'whatever you used to get the next row
end with

sourcerng.entirerow.copy _
destination:=destrng


====
But I think you've got a problem with the columnwidths. If you're pasting
several worksheets into one giant worksheet, then what columnwidth would you use
for the monster worksheet? Each of the several could have different
columnwidths.
 
Back
Top