Prev: Code to add gridlines to Excel Pivot Tables
Next: Uppercase
From: johncaleb on 6 May 2010 11:25
I need a macro to select and copy all non-blank cells in Sheet1, then paste
these cells into sheet2 at Cell A1.

thanks much!
From: Jacob Skaria on 6 May 2010 11:31
Try the below

Sub Macro()
Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
End Sub

'If you have any formulas that are to be converted to values then try this
version
Sub Macro()
Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
Sheets("Sheet2").UsedRange = Sheets("Sheet2").UsedRange.Value
End Sub

--
Jacob (MVP - Excel)


"johncaleb" wrote:

> I need a macro to select and copy all non-blank cells in Sheet1, then paste
> these cells into sheet2 at Cell A1.
>
> thanks much!
From: Gord Dibben on 6 May 2010 18:48
Jacob

You seem to be assuming the used range is a block of contiguous cells only

If blanks are interspersed throughout the usedrange they will be copied
also.

OP might as well just copy sheet1 to sheet2


Gord Dibben MS Excel MVP


On Thu, 6 May 2010 08:31:02 -0700, Jacob Skaria
<JacobSkaria(a)discussions.microsoft.com> wrote:

>Try the below
>
>Sub Macro()
>Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
>End Sub
>
>'If you have any formulas that are to be converted to values then try this
>version
>Sub Macro()
>Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
>Sheets("Sheet2").UsedRange = Sheets("Sheet2").UsedRange.Value
>End Sub

From: Jacob Skaria on 7 May 2010 00:40
Yes you are right. Not sure whether the OP meant to avoid blank
rows...eventhough its is mentioned as 'non-blank cells'

--
Jacob (MVP - Excel)


"Gord Dibben" wrote:

> Jacob
>
> You seem to be assuming the used range is a block of contiguous cells only
>
> If blanks are interspersed throughout the usedrange they will be copied
> also.
>
> OP might as well just copy sheet1 to sheet2
>
>
> Gord Dibben MS Excel MVP
>
>
> On Thu, 6 May 2010 08:31:02 -0700, Jacob Skaria
> <JacobSkaria(a)discussions.microsoft.com> wrote:
>
> >Try the below
> >
> >Sub Macro()
> >Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
> >End Sub
> >
> >'If you have any formulas that are to be converted to values then try this
> >version
> >Sub Macro()
> >Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1")
> >Sheets("Sheet2").UsedRange = Sheets("Sheet2").UsedRange.Value
> >End Sub
>
> .
>
 | 
Pages: 1
Prev: Code to add gridlines to Excel Pivot Tables
Next: Uppercase