I'm not an access user, so I can't answer that portion.
But I don't see a way to use another sheet that would make this work--but maybe
you can test your idea yourself.
You may be able to use a macro that would accomplish what you want.
This is pretty specific. It copy|pastes a single column of data.
If your range to copy looked like this (4 visible cells):
1
2
3
4
And your range to paste looked like (7 visible cells):
a
b
c
d
e
f
g
Then the result would be:
1
2
3
4
-
-
-
(where - means the cell is cleared. Who knows if that meets your
requirements???????)
If you select too many cells to copy and not enough cells to paste into, then
you'll get a warning message.
Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim vRngToCopy As Range
Dim RngToPaste As Range
Dim vRngToPaste As Range
Dim myCell As Range
Dim DestCell As Range
Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = Application.InputBox _
(prompt:="Select some cells to copy", Type:=8)
On Error GoTo 0
If RngToCopy Is Nothing Then
Exit Sub 'user hit cancel
End If
Set vRngToCopy = Nothing
On Error Resume Next
Set vRngToCopy = Intersect(RngToCopy, _
RngToCopy.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible))
On Error GoTo 0
If vRngToCopy Is Nothing Then
MsgBox "No Visible cells in the range to copy!"
Exit Sub
End If
Set RngToPaste = Nothing
On Error Resume Next
Set RngToPaste = Application.InputBox _
(prompt:="Select some cells to paste", Type:=8)
On Error GoTo 0
If RngToPaste Is Nothing Then
Exit Sub 'user hit cancel
End If
Set vRngToPaste = Nothing
On Error Resume Next
Set vRngToPaste = Intersect(RngToPaste, _
RngToPaste.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible))
On Error GoTo 0
If vRngToPaste.Cells.Count = 0 Then
MsgBox "No visible cells in the range to paste"
Exit Sub
End If
If vRngToPaste.Cells.Count < vRngToCopy.Cells.Count Then
MsgBox "Paste Range is too small!"
Exit Sub
End If
'clear existing values in the vRngToPaste
vRngToPaste.ClearContents
Set DestCell = Nothing
For Each myCell In vRngToCopy.Cells
Set DestCell = NextVisibleCell(vRngToPaste, DestCell)
DestCell.Value = myCell.Value
Next myCell
End Sub
Function NextVisibleCell(rng, myCell) As Range
Dim aCtr As Long
Dim myNextCell As Range
Set myNextCell = Nothing
If myCell Is Nothing Then
Set myNextCell = rng.Cells(1)
Else
For aCtr = 1 To rng.Areas.Count
If Intersect(myCell, rng.Areas(aCtr)) Is Nothing Then
'keep looking
Else
With rng
If myCell.Address _
= .Areas(aCtr) _
.Cells(.Areas(aCtr).Cells.Count).Address Then
'the last cell in that area
Set myNextCell = .Areas(aCtr + 1).Cells(1)
Else
Set myNextCell = myCell.Offset(1, 0)
End If
End With
'found it, so stop looking
Exit For
End If
Next aCtr
End If
Set NextVisibleCell = myNextCell
End Function
The line that clears out the range to paste is:
vRngToPaste.ClearContents
If you don't like that clearing, you could comment/delete that line and end up
with:
1
2
3
4
e
f
g
(instead of e,f,g being cleard).
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm