A sheet that I am working with uses hidden rows to do various bonus
features within a longer sheet. (a parts list)

Sometimes these parts are imported from a different sheet. My sheet is
set up to accommodate small, medium, or large numbers of parts. Today I
encountered a problem where someone tried to copy in so many items at
once that it overlapped a hidden row and caused an error due to trying
to write in locked cells.

What I would like to do is to have the user select the column that the
data will be imported into (could be description, qnty, price, etc) and
be able to press a button that will work in the following method (or a
better one if so suggested).

(I'm expecting data to already be in the clipboard.)
Save the location of the active cell when the button was pushed
Unlock a large selection of cells in an area of the form that is unused
(around "A500:A700")
Paste the values into this out of the way area. (probably count the
selection size, too)
Cut the first 40 entries.
Paste those entries into the area originally selected by the user.
Cut the next 48 entries.
Paste those entries into the next applicable area. (same column, row
offset by about 51)
Repeat the step with 48 entries.
If extra remains, notify the user that the selection was too big and
some data was truncated.
Delete any remaining data.
Lock the cells.

I'm having trouble with two areas. I can't figure out how to get usable
information from what cell was active when the macro is run. I can get
the row and the column separately, but don't know how to use them
after. Secondly, I'm having trouble with the destination I cut to.
(this related to my lack of knowledge of the first one, but I don’t
know how I should enter the cell co-ords).

Shown below is the smaller version I was playing with (I take baby
steps when writing programs because I'm still such a noob). Further
below that is how I have my variable size page set up incase some of
you are curious.

-Thanks

Sub Pastevalues()
co = ActiveCell.Column
ro = ActiveCell.Row
Range("A100").PasteSpecial Paste:=xlValues
Range("A100", "A104").Cut
Range("a1").Select
ActiveCell.Offset(ro, co).Select
ActiveCell.PasteSpecial xlPasteAll
Range("A104", "A110").Cut
Range("a1").Select
ActiveCell.Offset(ro, co).Select
ActiveCell.Offset(5, 0).Select
ActiveCell.PasteSpecial xlPasteAll
End Sub
__________________________________________________ ______________
Sub smallsheet(targ)
Call WSunlock(targ)
With Sheets(targ)
If .Range("z1").Value = 0 And .Range("z2").Value = 0 Then
Application.ScreenUpdating = False
..Rows("11:155").Hidden = False
..Rows("54:155").Hidden = True
Application.ScreenUpdating = True
Else
MsgBox "Existing values exceed requested sheet size.", vbOKOnly
End If
End With
Call WSlock(targ)
End Sub

Sub medsheet(targ)
Call WSunlock(targ)
With Sheets(targ)
If Range("z2").Value = 0 Then
Application.ScreenUpdating = False
Range("A11:J155").Select
Selection.EntireRow.Hidden = False
Range("A105:J155").Select
Selection.EntireRow.Hidden = True
Range("A51:A53").Select
Selection.EntireRow.Hidden = True
Application.ScreenUpdating = True
Else
MsgBox "Existing values exceed requested sheet size.", vbOKOnly
End If
End With
Call WSlock(targ)
End Sub

Sub largesheet(targ)
Call WSunlock(targ)
With Sheets(targ)
Application.ScreenUpdating = False
Range("A11:J155").Select
Selection.EntireRow.Hidden = False
Range("A51:A53").Select
Selection.EntireRow.Hidden = True
Range("A102:A104").Select
Selection.EntireRow.Hidden = True
Application.ScreenUpdating = True
End With
Call WSlock(targ)
End Sub


--
wilro85
------------------------------------------------------------------------
wilro85's Profile: http://www.excelforum.com/member.php...o&userid=26935
View this thread: http://www.excelforum.com/showthread...hreadid=493489

Nevermind, I found my answer in another users question.

I have to use "cells(x,y)

--
wilro8
-----------------------------------------------------------------------
wilro85's Profile: http://www.excelforum.com/member.php...fo&userid=2693
View this thread: http://www.excelforum.com/showthread.php?threadid=49348