VBA Code Help with Command Button

[VsiblyBasic]

I am attempting to have excel unhide a hidden data tab, copy one row from that tab, paste it to the next available row on a sheet chosen in a dropdown menu ("Plant") on the userform. (Hopefully that makes sense)

This is the code that I have as of this moment, it copies the data but does not paste it.

Private Sub NewEntry_Click()
Dim Actsheet As String

'Turn off Screen Updating'
Application.ScreenUpdating = False
On Error Resume Next

'Unhide Data Tab'
ActiveWorkbook.Sheets("Data").Visible = True

'Copy Row 17'
ActiveWorkbook.Sheets("Data").Rows(17).EntireRow.Copy


'Determine next available Row in new sheet and paste to it'
NextRow = Sheets(Range(Plant.Text).Value).Range("A" & Rows.Count).End(x1Up).Row + 1
ActiveWorkbook.Sheets(Range(Plant.Text).Value).Range("A" & nxtRw).Select
ActiveWorkbook.Sheets(Range(Plant.Text).Value).Range("A" & nxtRw).Paste

'Hide Data Tab'
ActiveWorkbook.Sheets("Data").Visible = False
Application.ScreenUpdating = True

'Select active sheet based on drop-box selection'
Dim actWSH As String
actWSH = Plant.Text
Worksheets(actWSH).Select

'Close this form'
Unload UserForm1

End Sub

[Paul_Hossler]

Comment out the On Error Resume Next and see if it fails somewhere

[VsiblyBasic]

(Duh, I forgot that would prevent if something was wrong haha).

So it gives me a Run-time error: "Method 'Range' of object '_Global' failed.

[VsiblyBasic]

22939(this is specifically what it highlights as the error which I had deleted before and I guess forgot to save)

[Paul_Hossler]

Assuming that Plant is a Textbox on the user form, and is supposed to be the name of the WS, maybe


Activeworkbook.Sheets(Plant.Text).Select

Also, you don't need to unhide the Data sheet and there's no need to select ranges to copy/paste





Option Explicit


Private Sub NewEntry_Click()

Dim Actsheet As String

'Turn off Screen Updating'
Application.ScreenUpdating = False

With ActiveWorkbook
'Determine next available Row in new sheet and paste to it'
NextRow = Sheets(Plant.Text).Range("A" & .Rows.Count).End(x1Up).Row + 1

'Copy Row 17'
.Sheets("Data").Rows(17).Copy Sheets(Plant.Text).Range("A" & nxtRw)

Worksheets(Plant.Text).Select
End With

Application.ScreenUpdating = True

'Close this form'
Unload UserForm1
End Sub

[VsiblyBasic]

So if I want it to specifically grab row 17 from the "data" tab which is a hidden sheet and paste it to the next available row on a different sheet I don't need the ".Range("A" & nxtRw)" portion?

I'm assuming that it looks something like this?

ActiveWorkbook.Sheets(Plant.Text).Rows(nxtRw, "A").Paste

[Paul_Hossler]

I added a bit to my #5 after I thought that my suggestion was a little too brief ...



.Sheets("Data").Rows(17).Copy Sheets(Plant.Text).Range("A" & nxtRw)

[VsiblyBasic]

Cool, I appreciate the help so far!

Now when I run I'm hitting an error on the
"NextRow = Sheets(Plant.Text).Range("A" & .Rows.Count).End(x1Up).Row + 1"

Saying that the object doesn't support this property or method.

I'm assuming I didn't set up the drop down menu properly.
I'm trying to work it as a combobox on the userform itself.

I have all the sheets named, the list of sheet names set up as a table on the data tab, and that's what the combobox is pulling from.

I can send more info if needed (I'm really new to this just learning as I go and from helpful people on the internet haha).

[Paul_Hossler]

In my example in post #5 I used With Activesheet ... End With

The <dot> in .Rows.Count in ...





NextRow = Sheets(Plant.Text).Range("A" & .Rows.Count).End(x1Up).Row + 1





means to use the .Parent, which in my example was assumed to be the Activesheet in the With/End With


So if you didn't use my "With Activesheet", then delete the <dot>

[VsiblyBasic]

Okay, here's the current state of the code, still giving me the same error:



Private Sub NewEntry_Click ()
Dim Actsheet As String
'Turn off Screen Updating'
Application.ScreenUpdating = False

With ActiveWorkbook
'Determine next available row in new sheet'
NextRow = Sheets(Plant.Text).Range("A" & Rows.Count).End(x1Up).Row + 1

'Copy Row 17'
.Sheets("Data").Rows(17).Copy Sheets(Plant.Text).Range("A" & nxtRw)

'Select active sheet based on drop box'
Dim actWsh As String
actWSH = Plant.Text
Worksheets(actWSH).Select

End With
'Screen updating back on'
Application.ScreenUpdating = True

'Close form'
Unload UserForm1

End Sub

[Paul_Hossler]

I didn't bother to make a userform, so I just forced in a Plant name for a sheet

This seems to work fine




Option Explicit

Sub test()
Dim wsActive As Worksheet, wsPlant As Worksheet
Dim NextRow As Long

Dim Plant As String

Plant = "PlantInNewYork" ' just for testing

'Turn off Screen Updating'
Application.ScreenUpdating = False

Set wsActive = ActiveSheet
Set wsPlant = Worksheets(Plant)

With ActiveWorkbook
'Determine next available row in new sheet'
NextRow = Sheets(Plant).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Row 17'
.Sheets("Data").Rows(17).Copy Sheets(Plant).Range("A" & NextRow)
wsPlant.Select
End With

'Screen updating back on'
Application.ScreenUpdating = True

'Close form'
'Unload UserForm1
End Sub