Pasting Blanks using a macro

TTomSawyer · #1 08-15-2005, 05:56 PM

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.

#2 08-15-2005, 07:05 PM

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" <TTomSawyer.1ttt2h_1124143531.57@excelforum-nospam.com> wrote
in message news:TTomSawyer.1ttt2h_1124143531.57@excelforum-nospam.com...
>
> 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
>

TTomSawyer · #3 08-15-2005, 10:10 PM

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

#4 08-16-2005, 03:05 AM

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" <TTomSawyer.1tu6yb_1124161545.5382@excelforum-nospam.com> wrote
in message news:TTomSawyer.1tu6yb_1124161545.5382@excelforum-nospam.com...
>
> 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
>

TTomSawyer · #5 08-16-2005, 10:28 AM

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

#6 08-16-2005, 12:05 PM

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" <TTomSawyer.1tv4al_1124204789.1909@excelforum-nospam.com> wrote
in message news:TTomSawyer.1tv4al_1124204789.1909@excelforum-nospam.com...
>
> 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
>

Welcome to the Excel Forum

Forum Statistics