Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying spreadsheets in directory into master spreadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying spreadsheets in directory into master spreadsheet
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. dtguitarfan wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying data from master ti individual sheets | Excel Discussion (Misc queries) | |||
How Do I Automatically Link New Spreadsheets to One Master File? | Excel Discussion (Misc queries) | |||
Automatically Linking New Spreadsheets to Master File | Excel Worksheet Functions | |||
Combining spreadsheets to a master sheet | Excel Discussion (Misc queries) | |||
Copying From Master | Excel Programming |