Prev: Geometric progression in VBA
Next: Choose which cell to paste data
From: johncaleb on 8 May 2010 09:42
Hi,
I tried this macro (below), but didn't work...Not sure what happened.
Please let me clarify my need...

I have Sheet1 open in WorkBook1 with data in the range A1:G200
I need a macro to select & copy the last 73 rows of data,in this case
A128:G200 and paste it on Sheet1 of WorkbookB starting at B10. Note this data
range can change from day to day.

Then copy the next set of 73 rows from the bottom, in this case A56:G127 and
Paste that on cell K1 WorkbookB Sheet1.

and so on until all the data is separated as such.

Please help again. thx

"JLGWhiz" wrote:

> Sub dk()
> Dim rng As Range
> Set rng = Workbooks(1).Sheets(1).UsedRange
> rng.Copy Workbooks(2).Sheets(2).Range("A1")
> Application.CutCopyMode = False
> Set wb2rng = Workbooks(2).Sheets(2).UsedRange
> With wb2rng
> .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
> End With
>
>
>
> End Sub
>
>
>
>
> "johncaleb" <johncaleb(a)discussions.microsoft.com> wrote in message
> news:BB58AD6A-AD7E-4D47-BB11-1C0C94747A43(a)microsoft.com...
> > hi again,
> >
> > I have 2 workbooks open. I need a macro to select and copy all non-blank
> > cells from Sheet1 FROM one workbook, then paste these cells into the 2nd
> > workbook, sheet2 at Cell A1.
> >
> > thanks much!
> >
>
>
> .
>

From: Don Guillett on 8 May 2010 10:02
I gave you an answer in your previous thread which you can easily modify.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"johncaleb" <johncaleb(a)discussions.microsoft.com> wrote in message
news:A1D210AF-B1C2-4121-BDF0-AC69C471E342(a)microsoft.com...
> Hi,
> I tried this macro (below), but didn't work...Not sure what happened.
> Please let me clarify my need...
>
> I have Sheet1 open in WorkBook1 with data in the range A1:G200
> I need a macro to select & copy the last 73 rows of data,in this case
> A128:G200 and paste it on Sheet1 of WorkbookB starting at B10. Note this
> data
> range can change from day to day.
>
> Then copy the next set of 73 rows from the bottom, in this case A56:G127
> and
> Paste that on cell K1 WorkbookB Sheet1.
>
> and so on until all the data is separated as such.
>
> Please help again. thx
>
> "JLGWhiz" wrote:
>
>> Sub dk()
>> Dim rng As Range
>> Set rng = Workbooks(1).Sheets(1).UsedRange
>> rng.Copy Workbooks(2).Sheets(2).Range("A1")
>> Application.CutCopyMode = False
>> Set wb2rng = Workbooks(2).Sheets(2).UsedRange
>> With wb2rng
>> .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
>> End With
>>
>>
>>
>> End Sub
>>
>>
>>
>>
>> "johncaleb" <johncaleb(a)discussions.microsoft.com> wrote in message
>> news:BB58AD6A-AD7E-4D47-BB11-1C0C94747A43(a)microsoft.com...
>> > hi again,
>> >
>> > I have 2 workbooks open. I need a macro to select and copy all
>> > non-blank
>> > cells from Sheet1 FROM one workbook, then paste these cells into the
>> > 2nd
>> > workbook, sheet2 at Cell A1.
>> >
>> > thanks much!
>> >
>>
>>
>> .
>>
>

From: JLGWhiz on 8 May 2010 14:27
I responded to your previous post for 5/7/2010



"johncaleb" <johncaleb(a)discussions.microsoft.com> wrote in message
news:A1D210AF-B1C2-4121-BDF0-AC69C471E342(a)microsoft.com...
> Hi,
> I tried this macro (below), but didn't work...Not sure what happened.
> Please let me clarify my need...
>
> I have Sheet1 open in WorkBook1 with data in the range A1:G200
> I need a macro to select & copy the last 73 rows of data,in this case
> A128:G200 and paste it on Sheet1 of WorkbookB starting at B10. Note this
> data
> range can change from day to day.
>
> Then copy the next set of 73 rows from the bottom, in this case A56:G127
> and
> Paste that on cell K1 WorkbookB Sheet1.
>
> and so on until all the data is separated as such.
>
> Please help again. thx
>
> "JLGWhiz" wrote:
>
>> Sub dk()
>> Dim rng As Range
>> Set rng = Workbooks(1).Sheets(1).UsedRange
>> rng.Copy Workbooks(2).Sheets(2).Range("A1")
>> Application.CutCopyMode = False
>> Set wb2rng = Workbooks(2).Sheets(2).UsedRange
>> With wb2rng
>> .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
>> End With
>>
>>
>>
>> End Sub
>>
>>
>>
>>
>> "johncaleb" <johncaleb(a)discussions.microsoft.com> wrote in message
>> news:BB58AD6A-AD7E-4D47-BB11-1C0C94747A43(a)microsoft.com...
>> > hi again,
>> >
>> > I have 2 workbooks open. I need a macro to select and copy all
>> > non-blank
>> > cells from Sheet1 FROM one workbook, then paste these cells into the
>> > 2nd
>> > workbook, sheet2 at Cell A1.
>> >
>> > thanks much!
>> >
>>
>>
>> .
>>
>


 | 
Pages: 1
Prev: Geometric progression in VBA
Next: Choose which cell to paste data