Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Multiple Areas from One Workbook to Another
Hello
I modified John Walkenbach’s CopyMultipleSelection to copy multiple areas from each worksheet in workbook AA to worksheet in workbook BB. All the sheets in BB will have the same name as those in AA. Example: Copy selected areas in worksheet “LA” in workbook AA to similarly named worksheet “LA” in workbook BB. Here are the modified codes. It errors out on the line SelAreas(i).Copy pasteRange.Offset(RowOffset, ColOffset). I would appreciate someone pointing me in the right direction. Sub CopyMultipleSelection() 'This code is in a module in workbook BB Dim qq As Integer: Dim tt As Integer Dim BB As Workbook: Set BB = ThisWorkbook Dim rAcells As Range: Dim SelAreas() As Range: Dim PasteRange As Range: Dim UpperLeft As Range: Dim NumAreas As Integer, i As Integer Dim TopRow As Long, LeftCol As Integer: Dim RowOffset As Long, ColOffset As Integer Application.Calculation = xlCalculationManual qq = 0 For tt = 1 To Workbooks.Count If Windows(Workbooks(tt).Name).Visible = True Then qq = qq + 1 If BB.Name < Workbooks(tt).Name Then Windows(Workbooks(tt).Name).Activate Range("F11").Value = BB.Name ' In workbook AA, set name of workbook BB End If End If Next tt If qq = 1 Then GoTo WarningMessage If qq 2 Then GoTo WarningMessage2 If BB.Name < Range("F11").Value Then Windows(Range("F11").Value).Activate 'activate workbook AA Set rAcells = ActiveSheet.Range("E15:CI86") Dim rNumTextcells As Range: On Error Resume Next: Set rNumTextcells = rAcells.SpecialCells(xlCellTypeConstants) 'select areas in AA to copy to workbook BB ActiveSheet.Range("F10") = ActiveSheet.Name 'name of worksheet in AA rNumTextcells.Select: 'areas selected to copy to worksheet in BB ' Store the areas as separate Range objects NumAreas = Selection.Areas.Count ReDim SelAreas(1 To NumAreas) For i = 1 To NumAreas Set SelAreas(i) = Selection.Areas(i) Next ' Determine the upper left cell in the multiple selection TopRow = ActiveSheet.Rows.Count LeftCol = ActiveSheet.Columns.Count For i = 1 To NumAreas If SelAreas(i).Row < TopRow Then TopRow = SelAreas(i).Row If SelAreas(i).Column < LeftCol Then LeftCol = SelAreas(i).Column Next Set UpperLeft = Cells(TopRow, LeftCol) On Error Resume Next On Error GoTo 0 ' Make sure only the upper left cell is used Set PasteRange = UpperLeft.Range("A1") Set PasteRange = Workbooks(Range("F11").Value).Worksheets(Range("F1 0").Value).Range(PasteRange.Address) 'determine the upper left cell in workbook BB For i = 1 To NumAreas RowOffset = SelAreas(i).Row - TopRow ColOffset = SelAreas(i).Column - LeftCol SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset) Next i Range("F11").ClearContents: Range("F10").ClearContents Application.Calculation = xlCalculationAutomatic Exit Sub WarningMessage: MsgBox ("Only 1 worksbook in windows - you need 2 workbooks to run this macro") Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Exit Sub WarningMessage2: MsgBox ("Only 2 worksbooks are allowed - the original workbook and the new workbook") Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Multiple Areas from One Workbook to Another
You may be making this more difficult than it needs to be? Both files
same sheet structure___? As I understand it you want to copy what part of each worksheet __________in the source file to the same sheet in the destination file. Where on the destination sheet_______. ======= On Oct 11, 8:50*am, jycpooh wrote: Hello I modified John Walkenbach’s CopyMultipleSelection to copy multiple areas from each worksheet in workbook AA to worksheet in workbook BB. All the sheets in BB will have the same name as those in AA. Example: Copy selected areas in worksheet “LA” in workbook AA to similarly named worksheet “LA” in workbook BB. Here are the modified codes. It errors out on the line SelAreas(i).Copy pasteRange.Offset(RowOffset, ColOffset). *I would appreciate someone pointing me in the right direction. Sub CopyMultipleSelection() 'This code is in a module in workbook BB Dim qq As Integer: Dim tt As Integer Dim BB As Workbook: Set BB = ThisWorkbook Dim rAcells As Range: Dim SelAreas() As Range: * *Dim PasteRange As Range: Dim UpperLeft As Range: * *Dim NumAreas As Integer, i As Integer Dim TopRow As Long, LeftCol As Integer: * *Dim RowOffset As Long, ColOffset As Integer Application.Calculation = xlCalculationManual qq = 0 For tt = 1 To Workbooks.Count *If Windows(Workbooks(tt).Name).Visible = True Then * *qq = qq + 1 * *If BB.Name < Workbooks(tt).Name Then * * *Windows(Workbooks(tt).Name).Activate * * *Range("F11").Value = BB.Name * ' In workbook AA, set name of workbook BB * *End If *End If Next tt If qq = 1 Then GoTo WarningMessage If qq 2 Then GoTo WarningMessage2 If BB.Name < Range("F11").Value Then Windows(Range("F11").Value).Activate * 'activate workbook AA Set rAcells = ActiveSheet.Range("E15:CI86") Dim rNumTextcells As Range: On Error Resume Next: Set rNumTextcells = rAcells.SpecialCells(xlCellTypeConstants) * 'select areas in AA to copy to workbook BB ActiveSheet.Range("F10") = ActiveSheet.Name * 'name of worksheet in AA rNumTextcells.Select: 'areas selected to copy to worksheet in BB ' * Store the areas as separate Range objects * * NumAreas = Selection.Areas.Count * * ReDim SelAreas(1 To NumAreas) * * For i = 1 To NumAreas * * * * Set SelAreas(i) = Selection.Areas(i) * * Next ' * Determine the upper left cell in the multiple selection * * TopRow = ActiveSheet.Rows.Count * * LeftCol = ActiveSheet.Columns.Count * * For i = 1 To NumAreas * * * * If SelAreas(i).Row < TopRow Then TopRow = SelAreas(i).Row * * * * If SelAreas(i).Column < LeftCol Then LeftCol = SelAreas(i).Column * * Next * * Set UpperLeft = Cells(TopRow, LeftCol) * * On Error Resume Next * * On Error GoTo 0 ' * Make sure only the upper left cell is used * * Set PasteRange = UpperLeft.Range("A1") * * Set PasteRange = Workbooks(Range("F11").Value).Worksheets(Range("F1 0").Value).Range(PasteRan ge.Address) 'determine the upper left cell in workbook BB * * For i = 1 To NumAreas * * * * RowOffset = SelAreas(i).Row - TopRow * * * * ColOffset = SelAreas(i).Column - LeftCol * * * * SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset) * * Next i * * Range("F11").ClearContents: Range("F10").ClearContents * * Application.Calculation = xlCalculationAutomatic Exit Sub WarningMessage: *MsgBox ("Only 1 worksbook in windows - you need 2 workbooks to run this macro") *Application.Calculation = xlCalculationAutomatic *Application.ScreenUpdating = True Exit Sub WarningMessage2: *MsgBox ("Only 2 worksbooks are allowed - the original workbook and the new workbook") *Application.Calculation = xlCalculationAutomatic *Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Multiple Areas from One Workbook to Another
On Oct 11, 9:49*am, Don Guillett wrote:
You may be making this more difficult than it needs to be? Both files same sheet structure___? As I understand it you want to copy what part of each worksheet __________in the source file to the same sheet in the destination file. Where on the destination sheet_______. Hi Don, Yes, both files same structure. I need to copy only constants and text values from workbook AA to BB. The reason I need a macro is because I may have 20 or more worksheets in workbook AA. These worksheets contain many cells with formula which I don't want to copy to corresponding worksheets in workbook BB. The named ranges in AA may have been redefined in BB so I only copy constants and text from each worksheet in AA to corresponding worksheet in BB. Any suggestion on why above code errors out would be most appreciated. Thanks Jim Chee Houston, TX |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Multiple Areas from One Workbook to Another
On Oct 11, 10:34*am, jycpooh wrote:
On Oct 11, 9:49*am, Don Guillett wrote: You may be making this more difficult than it needs to be? Both files same sheet structure___? As I understand it you want to copy what part of each worksheet __________in the source file to the same sheet in the destination file. Where on the destination sheet_______. Hi Don, Yes, both files same structure. I need to copy only constants and text values from workbook AA to BB. The reason I need a macro is because I may have 20 or more worksheets in workbook AA. These worksheets contain many cells with formula which I don't want to copy to corresponding worksheets in workbook BB. The named ranges in AA may have been redefined in BB so I only copy constants and text from each worksheet in AA to corresponding worksheet in BB. Any suggestion on why above code errors out would be most appreciated. Thanks Jim Chee Houston, TX I still don't know what you want but it is doable, probably using special cells. Send your file(s) with a complete explanation and before/after examples to |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Multiple Areas from One Workbook to Another
On Oct 11, 1:45*pm, Don Guillett wrote:
On Oct 11, 10:34*am, jycpooh wrote: On Oct 11, 9:49*am, Don Guillett wrote: You may be making this more difficult than it needs to be? Both files same sheet structure___? As I understand it you want to copy what part of each worksheet __________in the source file to the same sheet in the destination file. Where on the destination sheet_______. Hi Don, Yes, both files same structure. I need to copy only constants and text values from workbook AA to BB. The reason I need a macro is because I may have 20 or more worksheets in workbook AA. These worksheets contain many cells with formula which I don't want to copy to corresponding worksheets in workbook BB. The named ranges in AA may have been redefined in BB so I only copy constants and text from each worksheet in AA to corresponding worksheet in BB. Any suggestion on why above code errors out would be most appreciated. Thanks Jim Chee Houston, TX I still don't know what you want but it is doable, probably using special cells. Send your file(s) with a complete explanation and before/after examples to dguillett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup - in various areas within Workbook | Excel Worksheet Functions | |||
Copy rows from multiple workbook into a different workbook (sheet) | Excel Programming | |||
Cannot Format areas of a workbook | Excel Discussion (Misc queries) | |||
Set Multiple Print Areas | Excel Discussion (Misc queries) | |||
Copy & paste in multiple areas using VBA | Excel Discussion (Misc queries) |