Issue Copying Data from one workbook to another

I have a macro that selects a range of cells, A1:S21000, and copies data to a
new workbook, which is then saved as a .txt file. Is there a way to select
the range and only copy cells that have true values (not formulas)?

The source sheet contains formulas from row 1 to row 21000 to accommodate
for potential data input. In most cases, however, there will be many empty
rows. As my code is now, the entire range is copied to the new sheet,
including blank rows. Is there any way to easily eliminate these blank rows
from either the copied range before paste or from the destination sheet?

Incidentally, I tried selecting all of the blank rows from my destination
sheet, then delete-entire row. Immediately after, I hit ctrl+end and the
active cell still shows as S21000. What gives?

Any help would be greatly appreciated.
Mike


CODE:
Sub CMO_Export()
Range("A5:AA209").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=False
Columns("AA:AA").Select
Application.CutCopyMode = False
Selection.NumberFormat = "yyyy-mm-dd"
Range("A5:AA209").Cells
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

You have one line in your code that looks scary to me.

Range("A5:AA209").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

If you have an empty cell in column A:AA of any row, you want that row
deleted????

It would be more common (to me) to limit that to just a single column. Lots of
databases can have empty cells in non-critical fields.

And one way to clear those cells that look empty (after copy|paste
special|values) but aren't really is to:

select the range
edit|replace
what: (leave blank)
with: $$$$$ (some unique string
replace all

followed by
edit replace
what: $$$$$
with: (leave blank)
replace all

Now those cells that evaluated to ="" are really blank.

In code:


Option Explicit
Sub CMO_Export()

Dim ActWks As Worksheet
Dim newWks As Worksheet
Dim RngToCopy As Range

Set ActWks = ActiveSheet
Set RngToCopy = ActWks.Range("A5:AA209")

Set newWks = Workbooks.Add(1).Worksheets(1)

RngToCopy.Copy

With newWks
.Range("a1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False

.Range("aa:aa").NumberFormat = "yyyy-mm-dd"

.Range("a:a").Replace what:="", _
replacement:="$$$$$", lookat:=xlWhole, _
searchorder:=xlByRows, MatchCase:=False
.Range("a:a").Replace what:="$$$$$", _
replacement:="", lookat:=xlWhole, _
searchorder:=xlByRows, MatchCase:=False

.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

I did change your code to only look at column A, too.

Mike G - D.C. wrote:
>
> I have a macro that selects a range of cells, A1:S21000, and copies data to a
> new workbook, which is then saved as a .txt file. Is there a way to select
> the range and only copy cells that have true values (not formulas)?
>
> The source sheet contains formulas from row 1 to row 21000 to accommodate
> for potential data input. In most cases, however, there will be many empty
> rows. As my code is now, the entire range is copied to the new sheet,
> including blank rows. Is there any way to easily eliminate these blank rows
> from either the copied range before paste or from the destination sheet?
>
> Incidentally, I tried selecting all of the blank rows from my destination
> sheet, then delete-entire row. Immediately after, I hit ctrl+end and the
> active cell still shows as S21000. What gives?
>
> Any help would be greatly appreciated.
> Mike
>
> CODE:
> Sub CMO_Export()
> Range("A5:AA209").Select
> Selection.Copy
> Workbooks.Add
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=True, Transpose:=False
> Columns("AA:AA").Select
> Application.CutCopyMode = False
> Selection.NumberFormat = "yyyy-mm-dd"
> Range("A5:AA209").Cells
> .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End Sub

--

Dave Peterson

Dave -
Thanks for the help. I guess my code wasn't working at all, because rows
with blank cells weren't deleting. You are correct, that’s not the behavior
that I'm trying to drive. I'll give your code a try.
Thanks, again
Mike


"Dave Peterson" wrote:

> You have one line in your code that looks scary to me.
>
> Range("A5:AA209").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>
> If you have an empty cell in column A:AA of any row, you want that row
> deleted????
>
> It would be more common (to me) to limit that to just a single column. Lots of
> databases can have empty cells in non-critical fields.
>
> And one way to clear those cells that look empty (after copy|paste
> special|values) but aren't really is to:
>
> select the range
> edit|replace
> what: (leave blank)
> with: $$$$$ (some unique string
> replace all
>
> followed by
> edit replace
> what: $$$$$
> with: (leave blank)
> replace all
>
> Now those cells that evaluated to ="" are really blank.
>
> In code:
>
>
> Option Explicit
> Sub CMO_Export()
>
> Dim ActWks As Worksheet
> Dim newWks As Worksheet
> Dim RngToCopy As Range
>
> Set ActWks = ActiveSheet
> Set RngToCopy = ActWks.Range("A5:AA209")
>
> Set newWks = Workbooks.Add(1).Worksheets(1)
>
> RngToCopy.Copy
>
> With newWks
> .Range("a1").PasteSpecial Paste:=xlPasteValues, _
> Operation:=xlNone, SkipBlanks:=True, Transpose:=False
>
> .Range("aa:aa").NumberFormat = "yyyy-mm-dd"
>
> .Range("a:a").Replace what:="", _
> replacement:="$$$$$", lookat:=xlWhole, _
> searchorder:=xlByRows, MatchCase:=False
> .Range("a:a").Replace what:="$$$$$", _
> replacement:="", lookat:=xlWhole, _
> searchorder:=xlByRows, MatchCase:=False
>
> .Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End With
> End Sub
>
> I did change your code to only look at column A, too.
>
> Mike G - D.C. wrote:
> >
> > I have a macro that selects a range of cells, A1:S21000, and copies data to a
> > new workbook, which is then saved as a .txt file. Is there a way to select
> > the range and only copy cells that have true values (not formulas)?
> >
> > The source sheet contains formulas from row 1 to row 21000 to accommodate
> > for potential data input. In most cases, however, there will be many empty
> > rows. As my code is now, the entire range is copied to the new sheet,
> > including blank rows. Is there any way to easily eliminate these blank rows
> > from either the copied range before paste or from the destination sheet?
> >
> > Incidentally, I tried selecting all of the blank rows from my destination
> > sheet, then delete-entire row. Immediately after, I hit ctrl+end and the
> > active cell still shows as S21000. What gives?
> >
> > Any help would be greatly appreciated.
> > Mike
> >
> > CODE:
> > Sub CMO_Export()
> > Range("A5:AA209").Select
> > Selection.Copy
> > Workbooks.Add
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=True, Transpose:=False
> > Columns("AA:AA").Select
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "yyyy-mm-dd"
> > Range("A5:AA209").Cells
> > .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> > End Sub
>
> --
>
> Dave Peterson
>

And there is a potential problem in the code I posted.

If there are no empty cells in column A, then this line will fail:

.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

(since there isn't anything to delete)

Better would be to ignore the error if it occurs:

on error resume next
.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
on error goto 0

(Depending on your data, you may never see the problem--but it doesn't hurt to
try to make sure your code never blows up.)

Mike G - D.C. wrote:
>
> Dave -
> Thanks for the help. I guess my code wasn't working at all, because rows
> with blank cells weren't deleting. You are correct, that’s not the behavior
> that I'm trying to drive. I'll give your code a try.
> Thanks, again
> Mike
>
> "Dave Peterson" wrote:
>
> > You have one line in your code that looks scary to me.
> >
> > Range("A5:AA209").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> >
> > If you have an empty cell in column A:AA of any row, you want that row
> > deleted????
> >
> > It would be more common (to me) to limit that to just a single column. Lots of
> > databases can have empty cells in non-critical fields.
> >
> > And one way to clear those cells that look empty (after copy|paste
> > special|values) but aren't really is to:
> >
> > select the range
> > edit|replace
> > what: (leave blank)
> > with: $$$$$ (some unique string
> > replace all
> >
> > followed by
> > edit replace
> > what: $$$$$
> > with: (leave blank)
> > replace all
> >
> > Now those cells that evaluated to ="" are really blank.
> >
> > In code:
> >
> >
> > Option Explicit
> > Sub CMO_Export()
> >
> > Dim ActWks As Worksheet
> > Dim newWks As Worksheet
> > Dim RngToCopy As Range
> >
> > Set ActWks = ActiveSheet
> > Set RngToCopy = ActWks.Range("A5:AA209")
> >
> > Set newWks = Workbooks.Add(1).Worksheets(1)
> >
> > RngToCopy.Copy
> >
> > With newWks
> > .Range("a1").PasteSpecial Paste:=xlPasteValues, _
> > Operation:=xlNone, SkipBlanks:=True, Transpose:=False
> >
> > .Range("aa:aa").NumberFormat = "yyyy-mm-dd"
> >
> > .Range("a:a").Replace what:="", _
> > replacement:="$$$$$", lookat:=xlWhole, _
> > searchorder:=xlByRows, MatchCase:=False
> > .Range("a:a").Replace what:="$$$$$", _
> > replacement:="", lookat:=xlWhole, _
> > searchorder:=xlByRows, MatchCase:=False
> >
> > .Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> > End With
> > End Sub
> >
> > I did change your code to only look at column A, too.
> >
> > Mike G - D.C. wrote:
> > >
> > > I have a macro that selects a range of cells, A1:S21000, and copies data to a
> > > new workbook, which is then saved as a .txt file. Is there a way to select
> > > the range and only copy cells that have true values (not formulas)?
> > >
> > > The source sheet contains formulas from row 1 to row 21000 to accommodate
> > > for potential data input. In most cases, however, there will be many empty
> > > rows. As my code is now, the entire range is copied to the new sheet,
> > > including blank rows. Is there any way to easily eliminate these blank rows
> > > from either the copied range before paste or from the destination sheet?
> > >
> > > Incidentally, I tried selecting all of the blank rows from my destination
> > > sheet, then delete-entire row. Immediately after, I hit ctrl+end and the
> > > active cell still shows as S21000. What gives?
> > >
> > > Any help would be greatly appreciated.
> > > Mike
> > >
> > > CODE:
> > > Sub CMO_Export()
> > > Range("A5:AA209").Select
> > > Selection.Copy
> > > Workbooks.Add
> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=True, Transpose:=False
> > > Columns("AA:AA").Select
> > > Application.CutCopyMode = False
> > > Selection.NumberFormat = "yyyy-mm-dd"
> > > Range("A5:AA209").Cells
> > > .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> > > End Sub
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson