Pasting Blanks using a macro

[TTomSawyer]

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

 

[Norman Jones]

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.

 

[TTomSawyer]

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

 

[Norman Jones]

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
'<<=========================

 

[TTomSawyer]

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

 

[Norman Jones]

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
'<<=========================