Knowing where to paste

[Rob]

Excel 2000

I have recorded the following macro to create code to copy a range from one
workbook to a range in another workbook. I will refine code to include
variable of workbook names but in principal, the code works. However, there
are a couple of elements that don't work as required:

The following works where the range to be copied is altogether but fails to
copy everything when there's an empty cell in column B. I'm thinking of
using UsedRange.Select but unsure whether this is the right route?

Sub Sam1 ()
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub

The other problem is similar, I want to copy the used range (above code) to
another workbook (cell Sheet1!B3) then in an adjacent cell to the first row
of data copied (Sheet1!C3), enter a formula, this then needs to be copied
down to the corresponding last cell of the copied data. Again when one row
is empty in column B the code xlDown does work - Is there an alterative way
of filling cells ie. knowing how many rows were copied and the using this to
copy down? Any sample code to achieve would be most welcome.

Thanks, Rob

Sub Sam2()
ActiveCell.FormulaR1C1 = "'mytestformula"
Range("B3:C3").Select
Range("C3").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("C3:C180").Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=Fals
End Sub

Sun Test1 ()
Windows("risk.xls").Activate
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Columns("B:B").EntireColumn.AutoFit
Range("C3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "'mytestformula"
Range("B3:C3").Select
Range("C3").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("C3:C180").Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
End Sub

 

[mk]

The following works where the range to be copied is altogether but fails
to

copy everything when there's an empty cell in column B. I'm thinking of
using UsedRange.Select but unsure whether this is the right route?

Sub Sam1 ()
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub

Maybe you could copy the entire column B?:

Sub copy_entire_column_B()

Columns(2).Copy

End Sub

If you want to copy only range B4 and cells down and check if there is a
used cell
after an empty cell, use this:

Sub copy_range_with_empty_cells()

Dim myRange As Range
Dim tempRange As Range
Dim chk As Boolean

Set myRange = Range("B4").CurrentRegion
chk = False
Set tempRange = myRange.Resize(2, 1).Offset(myRange.Rows.Count, 0)

Do Until chk = True
If tempRange.Cells(1).Text = "" And tempRange.Cells(2).Text = ""
Then
chk = True
Else: chk = False
Set myRange = myRange.Resize(myRange.Rows.Count + 1, 1)
Set tempRange = myRange.Resize(2, 1).Offset(myRange.Rows.Count,
0)
End If

Loop

End Sub

 

[Rob]

Marcin,

Thanks, I like the second piece of code and will give it a go.

Rob