MACRO TO COPY AND PASTE!

[jay dean]

I need a mcro that would copy specific ranges of cells from sheetA in
WorkbookA.xls and paste-special the values into specific range of cells
in sheetB of WorkbookB.xls using an *interesting* pattern as follows--

-The macro should start by copying range E4:N10 of sheetA in
WorkbookA.xls and paste-special the values in cell E4 of sheetB in
WorkbookB.xls

-Then copy range E12:N18 of sheetA in WorkbookA.xls and paste-special
the values in cell E12 of sheetB in WorkbookB.xls

-Next, copy range E20:N26 of sheetA in WorkbookA.xls and
paste-special the values in cell E20 of sheetB in WorkbookB.xls

-Then copy range E28:N34 of sheetA in WorkbookA.xls and paste-special
the values in cell E28 of sheetB in WorkbookB.xls and so on.

*** Now you see the pattern. This macro should continue this copy and
pste-special values process in this pattern all the way down till there
is nothing to copy and paste.

Any assistance would be greatly appreaciated. Thanks!

Jay Dean

 

[Vasant Nanavati]

Hi Jay:

Something like (untested)

Sub CopyAndPaste()
Dim rng As Range
Set rng = Workbooks("WorkbookA.xls").Worksheets(1). _
Range("E4:N10")
Do Until WorksheetFunction.CountA(rng) = 0
rng.Copy
Workbooks("WorkbookB.xls").Worksheets(1). _
Range(rng.Address).PasteSpecial xlPasteValues
set rng = rng.Offset(8)
Loop
Application.CutCopyMode = False
End Sub

Regards,

Vasant.

 

[jay dean]

Your code appears to work fine but I would
like two slight modifications:

1. Instead of the macro stopping to copy and
paste-special values as soon as it meets a blank
range, I would like for it to continue with the
process *even* if the range it copies and pastes are
blank all the way down till the end of the sheets. The
reason is that sometimes blank ranges exist between
ranges with actual data to be copied.

2. Also, instead of the macro copying and
paste-special values, if I want it to just copy and
Link the ranges (i.e paste-special paste Link), how
would the code look like?

Thanks.
Jay Dean

 

[Vasant Nanavati]

Hi Jay:

Perhaps something like (untested):

Sub CopyAndPaste()
Dim rng As Range
Set rng = Workbooks("WorkbookA.xls").Worksheets(1). _
Range("E4:N10")
Do Until Intersect(rng, rng.Parent.UsedRange) Is Nothing
rng.Copy
With Workbooks("WorkbookB.xls").Worksheets(1)
.Activate
.Range(rng.Address).Resize(1, 1).Select
.Paste Link:=True
End With
Workbooks("WorkbookA.xls").Worksheets(1).Activate 'not sure if
necessary
Set rng = rng.Offset(8)
Loop
Application.CutCopyMode = False
End Sub

Regards,

Vasant.

 

[jay dean]

Great! I have two last things to ask-

1.Right now the code pastes the copied cells in a range equivalent to
the one it copied from. Excellent!
How will the code look if after copying the data I want it to
paste-pastes-pecial the values into any range I want , example, (start
pasting at cell E67)while keeping the same pattern.

2. How will it look if I wanted to let it start linking the copied
cells starting from E67, for example.

** Please, providing me with separate codes for the above two scenarios
will help me a lot. Thanks!

Jay Dean