'Copy to a variable range returns an error '438' Object doesn't support property or method
I need to copy information from one sheet (labeled Testsheet), copy it to the next available empty row on the third sheet (Testsheet3), then rearrange the columns into what a third party considers a readable order and paste that rearranged selection onto the next available row on the second sheet (Testsheet 2).
I can't get my copied or cut selections to paste into my variable range.
I realize this isn't optimal because this is my first foray into coding. I'm not looking for a massive rewrite, as it is important that I understand edits made so that I can fix the macro in the future if I have to.
Sub BottleresorttestFinal()
'Probably reduntant
Sheets("Testsheet").Activate
'Remove selection from internal clipboard as it progresses
Application.CutCopyMode = False
'Define my variable to find the last empty row for pasting
Dim NextEmptycell As Range
'Create an unsorted copy on a separate sheet
Sheets("Testsheet").Activate
Range(ActiveSheet.Range("A2"), ActiveSheet.Range("O2").End(xlDown)).Select
'I want the Macro to do nothing if there are no pasted selections below the O column
' since that will ALWAYS contain an integer is the row is populated
If ActiveSheet.Range("J2").Value = "" Then Exit Sub
Selection.Copy
Sheets("Testsheet3").Activate
'Set the range of my variable to the next available empty row
Set NextEmptycell = Sheets("Testsheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
NextEmptycell.Select
ActiveSheet.Selection.Paste ''' This is where the error occurs
'Activate the correct sheet
Sheets("Testsheet").Activate
'Select the entire Bottles column below the Description bar
Range(ActiveSheet.Range("J2"), ActiveSheet.Range("J2").End(xlDown)).Select
'Cut the selection
Selection.Cut
'Select the Cell to move the copied Cell next to (Cell 2, Item column)
Range("Store").Rows(2).Select
'Insert the selection to the right of Cell 2 in the "Items" column
Selection.Insert Shift:=xlToRight
'Select All below Row 1
Range(Cells(2, 15), Cells(Range("A1000000").End(xlUp).Row, 1)).Select
'Cut the data from "Testsheet, Row 2"
Selection.Cut
'Activate the destination worksheet "Testsheet2"
Sheets("Testsheet2").Activate
'Select the next empty cell for pasting
Set NextEmptycell = Range("A" & Rows.Count).End(xlUp).Offset(1)
'Paste in the target destination "Row 2"
NextEmptycell.Select
ActiveSheet.Selection.Paste
'Activate the destination worksheet "Testsheet" to confirm that it is empty
' as it should be and ready for the next paste-in to be resorted
Sheets("Testsheet").Activate
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|