Copying Ranges to different Workbooks
Posted by Joe Cunningham on June 27, 2001 3:45 PM
Hi!
I have this problem - I am creating several sub-workbooks from a a master workbook. Based on values in the first column of the master workbook, I create each sub workbook (in my case, the values are office location, a string). I can create the different workbooks just fine, but what I also need to do is copy the data associated with each location into the newly created workbook.
So I tried creating a range object that is defined on the master workbook and bounded by the first cell of the office location 10 and the lower right cell of the last office location 10. Then I create a range object that specifies the upper left hand corner on the child workbook ("A3"). I then use MasterRange.copy SubRange. This does not copy the data.
Is there some other method of copying ranges from one workbook to another? None of my Excel books are particularly helpful and MSDN has so little information (and too many Mickey Mouse examples).
Any help would be appreciated ...
Posted by Joe Cunningham on June 27, 2001 4:56 PM
Ok I was able to get this to work but had to hard-code my Range a little. Here is a code snippet
For i = 1 To arraySize
hold = ActiveWorksheet.Cells(upperRow, 1)
Do While hold = LocNames(i)
lowerRow = lowerRow + 1
j = j + 1
hold = ActiveWorksheet.Cells(lowerRow, 1)
Loop
'Here is the hardCoding I need to get rid of
Set masterRange = Range("A" & upperRow & ":S" & (lowerRow - 1))
Set TempWorkBook(i) = Workbooks.Add(Template:=TemplatePath)
TempWorkBook(i).SaveAs Filename:=TargetDir & "\Loc" & LocNames(i), FileFormat:=xlNormal
'setting target range on newly created workbook
Set targetRange = Range("A3")
'copying one range into the other
masterRange.Copy targetRange
TempWorkBook(i).Save
TempWorkBook(i).Close
upperRow = lowerRow
Next i
