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?


--
dtguitarfan
------------------------------------------------------------------------
dtguitarfan's Profile: http://www.excelforum.com/member.php...o&userid=24403
View this thread: http://www.excelforum.com/showthread...hreadid=380065