Hi,

I have a macro and a button setup to copy and paste a coupld column
and rows into another worksheet. The pasting material is differen
columns (name, date, amount spent, etc) with several rows for th
different information from each individual input. There are 30 rows i
total. The macro highlights all 30 rows then over 4 columns. I
copies the material, goes to the other worksheet with similar headings
finds the last entered row, and pastes the information into the sheet.


The problem is:
When the macro copies formulas that end up equaling "" (nothing there)
it copies something? but it doesnt appear anywhere when it is pasted
HOWEVER, when the macro goes to the last entered row, it takes i
account the blanks from the previous paste. How can I get rid o
these, so it pastes directly after the last REAL number, instead o
several "blanks"?

Does this make sense? I can send the sheet in order to show you wha
it looks like

--
TTomSawye
-----------------------------------------------------------------------
TTomSawyer's Profile: http://www.excelforum.com/member.php...fo&userid=2051
View this thread: http://www.excelforum.com/showthread.php?threadid=39594

Hi TTomSawyer,

In the absence of your code, perhaps data could be copied as values rather
than formulas.

If this is not a viable option, it would be necessary to evaluate the cell
which determines the last row on the target sheet, to determine if it
contains a formula which resolves to an empty string.

Post the button code and indicate if values may be copied to the target
sheet.


---
Regards,
Norman



"TTomSawyer" wrote
in message ...

Hi,

I have a macro and a button setup to copy and paste a coupld columns
and rows into another worksheet. The pasting material is different
columns (name, date, amount spent, etc) with several rows for the
different information from each individual input. There are 30 rows in
total. The macro highlights all 30 rows then over 4 columns. It
copies the material, goes to the other worksheet with similar headings,
finds the last entered row, and pastes the information into the sheet.


The problem is:
When the macro copies formulas that end up equaling "" (nothing there),
it copies something? but it doesnt appear anywhere when it is pasted.
HOWEVER, when the macro goes to the last entered row, it takes in
account the blanks from the previous paste. How can I get rid of
these, so it pastes directly after the last REAL number, instead of
several "blanks"?

Does this make sense? I can send the sheet in order to show you what
it looks like.


--
TTomSawyer
------------------------------------------------------------------------
TTomSawyer's Profile:
http://www.excelforum.com/member.php...o&userid=20511
View this thread: http://www.excelforum.com/showthread...hreadid=395945

Alright,

So these are the functions that i am copying and pasting from:

=IF(D13="","","Cash")

So if the box d13 has somethign in it, it says cash, otherwise it does
nothing, sorta.

the macro for pasting from one sheet to the other is below:

ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("MASTER Pettycash and CC").Select
Range("B7").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.Copy
Sheets("PETTY CASH INPUT").Select
Range("Y6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Range("C154:K185").Select
Selection.Copy
Sheets("MASTER Pettycash and CC").Select
Range("B7").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Range("B7").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.Copy
Sheets("PETTY CASH INPUT").Select
Range("AA6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
ActiveWindow.ScrollColumn = 1
Range("B6:F37").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C4").Select
Selection.ClearContents
Range("AA6").Select
End Sub


--
TTomSawyer
------------------------------------------------------------------------
TTomSawyer's Profile: http://www.excelforum.com/member.php...o&userid=20511
View this thread: http://www.excelforum.com/showthread...hreadid=395945

Hi TTomSawyer,

Try the following on a *copy* of your workbook:

'========================
Sub Tester()
Dim rng As Range
Dim rng2 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Application.ScreenUpdating = False

Set ws1 = Sheets("MASTER Pettycash and CC")
Set ws2 = Sheets("PETTY CASH INPUT")

ws1.Range("B7", Range("B7"). _
End(xlDown)).Offset(0, -1).Resize(, 2).Copy

With ws2
.Range("Y6").PasteSpecial _
Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False

.Range("C154:K185").Copy

Set rng = ws1.Columns("B")

Set rng2 = rng.Find(What:="*", _
After:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, _
SearchDirection:=xlPrevious).Offset(0, -1)

Set rng2 = Nothing

Set rng2 = rng.Find(What:="*", _
After:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, _
SearchDirection:=xlPrevious).Offset(0, -1)

.Range("C154:K185").Copy

rng2.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False

ws1.Range("B7", Range("B7"). _
End(xlDown)).Offset(0, -1).Resize(, 2).Copy

.Range("AA6").PasteSpecial _
Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False

.Range("C4").ClearContents
.Range("B6:F37").ClearContents
End With

Application.ScreenUpdating = False

End Sub
'<<=========================


---
Regards,
Norman


"TTomSawyer" wrote
in message ...

Alright,

So these are the functions that i am copying and pasting from:

=IF(D13="","","Cash")

So if the box d13 has somethign in it, it says cash, otherwise it does
nothing, sorta.

the macro for pasting from one sheet to the other is below:

ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("MASTER Pettycash and CC").Select
Range("B7").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.Copy
Sheets("PETTY CASH INPUT").Select
Range("Y6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Range("C154:K185").Select
Selection.Copy
Sheets("MASTER Pettycash and CC").Select
Range("B7").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Range("B7").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.Copy
Sheets("PETTY CASH INPUT").Select
Range("AA6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
ActiveWindow.ScrollColumn = 1
Range("B6:F37").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C4").Select
Selection.ClearContents
Range("AA6").Select
End Sub


--
TTomSawyer
------------------------------------------------------------------------
TTomSawyer's Profile:
http://www.excelforum.com/member.php...o&userid=20511
View this thread: http://www.excelforum.com/showthread...hreadid=395945

Hrm,
this didnt work, it resulted in a few errors.
specifically regarding the line with


ws1.Range("B7", Range("B7"). _
End(xlDown)).Offset(0, -1).Resize(, 2).Copy


--
TTomSawyer
------------------------------------------------------------------------
TTomSawyer's Profile: http://www.excelforum.com/member.php...o&userid=20511
View this thread: http://www.excelforum.com/showthread...hreadid=395945

Hi TTomSawyer.

Correcting qualification errors, again on a copy, try:

Sub Tester02()
Dim rng As Range
Dim rng2 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Application.ScreenUpdating = False

Set ws1 = Sheets("MASTER Pettycash and CC")
Set ws2 = Sheets("PETTY CASH INPUT")

With ws1
.Range(.Range("B7"), .Range("B7"). _
End(xlDown)).Offset(0, -1).Resize(, 2).Copy
End With
With ws2
.Range("Y6").PasteSpecial _
Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False

.Range("C154:K185").Copy

Set rng = ws1.Columns("B")

Set rng2 = rng.Find(What:="*", _
After:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, _
SearchDirection:=xlPrevious).Offset(0, -1)

Set rng2 = Nothing

Set rng2 = rng.Find(What:="*", _
After:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, _
SearchDirection:=xlPrevious).Offset(0, -1)

.Range("C154:K185").Copy

rng2.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False
End With

With ws1
.Range(.Range("B7"), .Range("B7"). _
End(xlDown)).Offset(0, -1).Resize(, 2).Copy
End With

With ws2
.Range("AA6").PasteSpecial _
Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False

.Range("C4").ClearContents
.Range("B6:F37").ClearContents
End With

Application.ScreenUpdating = False

End Sub
'<<=========================


---
Regards,
Norman



"TTomSawyer" wrote
in message ...

Hrm,
this didnt work, it resulted in a few errors.
specifically regarding the line with


ws1.Range("B7", Range("B7"). _
End(xlDown)).Offset(0, -1).Resize(, 2).Copy


--
TTomSawyer
------------------------------------------------------------------------
TTomSawyer's Profile:
http://www.excelforum.com/member.php...o&userid=20511
View this thread: http://www.excelforum.com/showthread...hreadid=395945