Help with using Range.End in nested tables

[dazedandconf]

I'm trying to copy specific series of cells in a nested table in one document and past them into another nested table in a second table.
The line
pastRange.End = rosterTable.Cell(4, 2).Range.End throws the error "Object Required".
I've used the exact same code layout earlier in the same Sub....
I'm also concerned that the
For Each tableRoster... is not looping.
Any help or suggestions gratefully reveived, thank you.


Sub CopyAndPasteNestedTables()
Dim WeeklyRotation As Document
Dim RWVB_Roster_Test As Document
Dim sourceDoc As String
Dim targetDoc As String
Dim weekTable As Table
Dim rosterTable As Table
Dim copyRange As Range
Dim pasteRange As Range
Dim tableNumber As Integer

' Set references to the source and target documents
sourceDoc = "C:\Users\richa\OneDrive\Documents\2024 Care\WeeklyRotation.docm"
targetDoc = "C:\Users\richa\OneDrive\Documents\2024 Care\RWVB_Roster_Test.docm"

Set WeeklyRotation = Documents.Open(FileName:=sourceDoc)
Set RWVB_Roster_Test = Documents.Open(FileName:=targetDoc)
Set rosterTable = RWVB_Roster_Test.Tables(1).Tables(1)
Let tableNumber = 1

'Document = RWVB_Roster_test
'Table = RWVB_Roster_test.Tables(1)
'Nestted Table = RWVB_Roster_test.Tables(1).Tables(1)

' Loop through each nested table in RWVB_Roster_Test
For Each rosterTable In RWVB_Roster_Test.Tables(1).Tables
' Set references to the corresponding tables in WeeklyRotation
Set weekTable = WeeklyRotation.Tables(1).Tables(tableNumber)
' Extract the desired range from the nested table in WeeklyRotation
Set copyRange = weekTable.Cell(2, 2).Range
copyRange.End = weekTable.Cell(3, 2).Range.End 'expression. Cell( _Row_ , _Column_ )
copyRange.Select

' Set the target range in RWVB_Roster_Test
Windows("RWVB_Roster_Test.docm").Activate
Set rosterTable = RWVB_Roster_Test.Tables(1).Tables(tableNumber)
Set pasteRange = rosterTable.Cell(3, 2).Range
pastRange.End = rosterTable.Cell(4, 2).Range.End

pastRange.Select

' Copy from WeeklyRotation and paste into RWVB_Roster_Test
copyRange.Copy
pasteRange.Paste
tableNumber = tableNumber + 1
Next rosterTable
End Sub

[Vladimir]

Hi! Choose either pastRange or pasteRange, because you are using both:
Set pasteRange = rosterTable.Cell(3, 2).Range
pastRange.End = rosterTable.Cell(4, 2).Range.End

pastRange.Select

[dazedandconf]

Thank you so much! This has been driving me nuts!!! :thumb

[dazedandconf]

Okay, no problem with the For loop. It appears to be working.
The copy range selects the two cells to be copied.


Set copyRange = weekTable.Cell(2, 2).Range
copyRange.End = weekTable.Cell(3, 2).Range.End
copyRange.Select 'test to verify cells are selected (two cells selected!)






How ever, there is still a problem with the paste range selection.


Windows("RWVB_Roster_Test.docm").Activate
Set rosterTable = RWVB_Roster_Test.Tables(1).Tables(tableNumber)
Set pasteRange = rosterTable.Cell(3, 2).Range
pasteRange.End = rosterTable.Cell(4, 2).Range.End

pasteRange.Select 'test to verify cells are selected (only the second cell is selected!)

' Copy from WeeklyRotation and paste into RWVB_Roster_Test
copyRange.Copy
pasteRange.Paste

The line
pasteRange.Paste throws an error "This command is not available."

Again, any help gratefully accepted.

[Vladimir]

Hi! I think the error is returned because the source doc is not activated. There are 2 solutions (though not tested by me): 1) before copyRange.Copy, insert a line to active WeeklyRotation, then insert another line to activate RWVB_Roster_Test & then paste the copied range; 2) (seems to be simpler) just move copyRange.Copy to be after copyRange.Select (thus, there's no need to activate the source doc). I hope either of the suggestions will work.

[Vladimir]

Correction:
"a line to active WeeklyRotation"
should be
"a line to activate WeeklyRotation"

[dazedandconf]

Thanks for your help Vladimir! It's working now, just needs a little tweeking! :clap:

[Vladimir]

You are welcome, dazedandconf (http://www.vbaexpress.com/forum/member.php?88427-dazedandconf)!

http://www.vbaexpress.com/forum/images/statusicon/user-offline.png