range help

[Cesar Zapata]

Hello,

Am i having a brain freeze or is this a bug?

I have 2 workbooks so I need to copy the below range in the main
workbook to the activesheet.


thisworkbook.Sheets("sheet2").Range(Cells(2, 3), Cells(2, 10)).Copy '
this is the main workbook



but I get this error applcation-defined object deifned error.

even if I select the mainworkbook sheet1 still get the error but if i
select the mainworkbook sheet2 is works ok.

I hope you can help me.

thanks,

Cesar

 

[Frank Kabel]

Hi
you may post your complete macro.

 

[Cesar Zapata]

Hi
you may post your complete macro.

''Here,

Sub formatactive()


Dim test As String



Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
Columns("B:B").EntireColumn.AutoFit
Columns("B:B").Select
Range("B85").Activate
Selection.NumberFormat = "0"
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.ScrollRow = 1
Range("B1").Select

fieldrange = ThisWorkbook.Sheets("sheet2").Range("a1:a23")
sheetname = ActiveSheet.Name

test = Application.Match(sheetname, fieldrange, False)

Sheets("sheet2").Range(Cells(test, 3), Cells(test, 10)).Copy



ActiveSheet.Range("a1").Select

ActiveSheet.Paste

End Sub


Thanks,

Cesar

 

[Harald Staff]

"Cesar Zapata" <[email protected]> skrev i melding

Hi Cesar

fieldrange = ThisWorkbook.Sheets("sheet2").Range("a1:a23")

Should probably read
Set fieldrange = ThisWorkbook.Sheets("sheet2").Range("a1:a23")

Sheets("sheet2").Range(Cells(test, 3), Cells(test, 10)).Copy

Cells(test, 3) refers to cells in the active sheet. Try
Sheets("sheet2").Range(Sheets("sheet2").Cells(test, 3),
Sheets("sheet2").Cells(test, 10)).Copy

HTH. Best wishes Harald

 

[Frank Kabel]

Hi
one question:
what are you trying to do with the match function 8looking for the
worksheetname in a number of values??). Hope this is what you want:

Sub formatactive()
Dim wks_target As Worksheet
Dim wks_source As Worksheet
Dim test As String
Dim fieldrange
Dim sheetname
Dim copy_rng As Range
Set wks_target = ActiveSheet
With wks_target
.Rows("1:1").Insert Shift:=xlDown
.Columns("B:B").EntireColumn.AutoFit
.Range("B85").NumberFormat = "0"
End With

Set wks_source = ActiveWorkbook.Worksheets("sheet2")

'Don't know what you're trying to do in the next statements. See
comments:

'fieldrange = ThisWorkbook.Sheets("sheet2").Range("a1:a23")
' this assigns not a range but the values of A1:A23 to an array
' the variable currently is not defined!

'sheetname = ActiveSheet.Name
'also sheetname is not defined

'test = Application.Match(sheetname, fieldrange, False)
' That are you trying to do with this statement

test = 2
wks_source.Activate
Set copy_rng = wks_source.Range(Cells(test, 3), Cells(test, 10))
copy_rng.Copy _
Destination:=wks_target.Range("A1")
wks_target.Activate

End Sub