HI! I've got code

Sub Transpose()
'declarations

Workbooks.Add

Set destination = ActiveWorkbook.Sheets(1)
Set difference = ActiveWorkbook.Sheets(2)

ThisWorkbook.Activate

  'Code to transpose data.it's a loop

'Then another code.

Set Target_Cell = destination.Range("C1")
Set Source_Cell = difference.Range("c1")

Const COMPANY_OFFSET = 3
Const COMPANY_COUNT = 3
For j = 1 To COMPANY_COUNT ' Loop through companies
  'Set range of Source from top to next to bottom of values
  Set Source = Range(Source_Cell, Source_Cell.End(xlDown).Offset(-1, 0))
  Set Target = Target_Cell
  Source.Select
  For Each cell In Source
    Target.Value = cell.Offset(1, 0) - cell.Value
    Set Target = Target.Offset(1, 0) ' Move target to next row
  Next
  Set Source_Cell = Source_Cell.Offset(0, COMPANY_OFFSET)
  Set Target_Cell = Target_Cell.Offset(0, COMPANY_OFFSET)
Next j

End Sub

My problem is,the 2nd code suppose to refer to the new workbook that has been added but it refer to the current activeworkbook. PLease help.

What do you mean by 2nd code. Which variable is giving the problem?

there are 2 code there. I did not put the 1st code bcos it works fine. The one I showed above is the one giving me probs. The one I bold is giving me probs.

Now I understand the problem.
What is happening is that you create a new workbook and then variables that point to the workbook:

Workbooks.Add

Set destination = ActiveWorkbook.Sheets(1)
Set difference = ActiveWorkbook.Sheets(2)

Next you activate the original workbook:

ThisWorkbook.Activate

and then try to select a range in the new workbook when it is no longer active.

Source.select

You have to first activate the worksheet before doing the select

Source.Parent.Activiate
Source.select

Enjoy!

Barry

hi, thanks barry, that solve the problem! :D