range class error

[steven_thomas]

Help Please !!!! ... I keep getting a range class error ... the macro should activate a 2nd workbook then activate the required worksheet and then copy a range of data before pasting to the original workbook ...


Dim number As Range
Set number = Range("c1")


'Hazard Risk Upload
Workbooks("Risk Register Assessment and Control Plan Tool v16.xls").Worksheets("Data Upload").Activate
Range("A178:A294").Select
Selection.Copy

Workbooks("Risk Assessment Tool Master Roll Up v1.xls").Worksheets("RAT Data Upload").Activate
Range("a55").Select
For counter = 1 To 240
If counter <= number Then
ActiveCell.Offset(0, 1).Activate
End If
Next

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

[Toppers]

Steven,
Try this. I assume you want data placed NUMBERcolumns along
in row 55.

HTH

Dim number As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Set number = Range("c1").Value



Set ws1 = Workbooks("Risk Register Assessment and Control Plan
Toolv16.xls").Worksheets("Data Upload")
Set ws2 = Workbooks("Risk Assessment Tool Master Roll
Upv1.xls").Worksheets("RAT Data Upload")


ws1.Range("A178:A294").Copy
ws2.Range("a55").Offset(0, number).PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False


"steven_thomas" wrote:

>
> Help Please !!!! ... I keep getting a range class error ... the macro
> should activate a 2nd workbook then activate the required worksheet and
> then copy a range of data before pasting to the original workbook ...
>
>
> Dim number As Range
> Set number = Range("c1")
>
>
> 'Hazard Risk Upload
> Workbooks("Risk Register Assessment and Control Plan Tool
> v16.xls").Worksheets("Data Upload").Activate
> Range("A178:A294").Select
> Selection.Copy
>
> Workbooks("Risk Assessment Tool Master Roll Up
> v1.xls").Worksheets("RAT Data Upload").Activate
> Range("a55").Select
> For counter = 1 To 240
> If counter <= number Then
> ActiveCell.Offset(0, 1).Activate
> End If
> Next
>
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
>
>
> --
> steven_thomas
> ------------------------------------------------------------------------
> steven_thomas's Profile: http://www.excelforum.com/member.php...o&userid=27568
> View this thread: http://www.excelforum.com/showthread...hreadid=502618
>
>

[Ron de Bruin]

Hi

Your problem is that you offset the column and there are only 256 columns
Do you want to offset rows ???

' For counter = 1 To 240
' If counter <= number Then
' ActiveCell.Offset(0, 1).Activate
' End If
' Next



This is working

Set number = Range("c1")
use the cell on the activesheet do you know that

Sub test()
Dim number As Range
Dim counter As Long
Set number = Range("c1")


'Hazard Risk Upload
Workbooks("Risk Register Assessment and Control Plan Tool v16.xls") _
.Worksheets("Data Upload").Range("A178:A294").Copy

Workbooks("Risk Assessment Tool Master Roll Up v1.xls").Worksheets("RAT Data Upload").Activate
Range("a55").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"steven_thomas" <[email protected]> wrote in message
news:[email protected]...
>
> Help Please !!!! ... I keep getting a range class error ... the macro
> should activate a 2nd workbook then activate the required worksheet and
> then copy a range of data before pasting to the original workbook ...
>
>
> Dim number As Range
> Set number = Range("c1")
>
>
> 'Hazard Risk Upload
> Workbooks("Risk Register Assessment and Control Plan Tool
> v16.xls").Worksheets("Data Upload").Activate
> Range("A178:A294").Select
> Selection.Copy
>
> Workbooks("Risk Assessment Tool Master Roll Up
> v1.xls").Worksheets("RAT Data Upload").Activate
> Range("a55").Select
> For counter = 1 To 240
> If counter <= number Then
> ActiveCell.Offset(0, 1).Activate
> End If
> Next
>
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
>
>
> --
> steven_thomas
> ------------------------------------------------------------------------
> steven_thomas's Profile: http://www.excelforum.com/member.php...o&userid=27568
> View this thread: http://www.excelforum.com/showthread...hreadid=502618
>