Copy data one excel sheet to other sheet

Hello,

any one help meee please im running that macro and getting data from one sheet to other one issue i have when i run that macro the window open to browze the speacific sheet i get the data but each and every time i have to select the sheet example if i have 100 cell to get the data i have to select the sheet 100 times

any solution urgently edit my macro and send me at my email
furrukh_cancer@yahoo.com




SDim RptWorkbook As Workbook
Dim myBufferRange As Range
Dim myBufRange2 As Range
Dim rDayRef As Range
Dim rNewDay As Range
Dim rLatestYTD As Range
Dim myDate As Date
Dim myValueBuffer As Double
Dim myLoopBuffer As Integer
Dim myAverageRef As Integer
Dim myZoom As Integer
Dim myScroll As Integer
Dim myBufferString As String
Dim myDrive As String * 1
Dim myDirectory As String



Sub DataCollector_PLANT__SEPERATOR_PRODUCTION_SUMMARY()
' &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
' Averaging line reference row 1
'Dim Var_path As String
'myAverageRef = 1
'Set rDayRef = ThisWorkbook.Sheets("All Oil Tank").Range("D2")
myDrive = "C:\"
myDirectory = "C:\Local\DataGather\DataMacro"

' -------------------------------------------------------------------------------------

myDate = DateValue("01-August-2010")
i = 4
Do While myDate <= DateValue("02-August-2010")

'------------------------------------'

myDay = Format(myDate, "d")
myMonth = Format(myDate, "mmmm")
myYear = Format(myDate, "yyyy")

If Format(myDate, "d") = 1 Or Format(myDate, "d") = 21 Or Format(myDate, "d") = 31 Then
suffix = "st"
Else
If Format(myDate, "d") = 2 Or Format(myDate, "d") = 22 Then
suffix = "nd"
Else
If Format(myDate, "d") = 3 Or Format(myDate, "d") = 23 Then
suffix = "rd"
Else
suffix = "th"
End If
End If
End If


'Hours online(main)

Range("F" & i) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$J$37"


'Sep Operating Pressure(psig-Main)

Range("F" & i + 1) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$J$38"

'Sep Operating Temprature(F-Main)

Range("F" & i + 2) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$J$39"

'Sep Gas Throughput (MMscf-main)

Range("F" & i + 3) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$J$40"


'Sep Condensate Throughput (bbls-main)


Range("F" & i + 4) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$J$42"


'Sep Water Throughput (bbls)



Range("F" & i + 5) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$J$44"


'Well gas Flared off-line during well testing operations (MMscf-Main)

Range("F" & i + 6) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$J$46"


'Hours online(test)

Range("F" & i + 7) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$N$37"

'Sep Operating Pressure(Test)

Range("F" & i + 8) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$N$38"

'Sep Operating Temprature(F-Test)

Range("F" & i + 9) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$N$39"

'Sep Gas Throughput (MMscf-Test)

Range("F" & i + 10) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$N$40"


'Sep Condensate Throughput (bbls-Test)


Range("F" & i + 11) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$N$42"


'Sep Water Throughput (bbls-test)



Range("F" & i + 12) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$N$44"


'Well gas Flared off-line during well testing operations (MMscf-test)


Range("F" & i + 13) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xls]" & myDay & suffix & "'!$N$46"



i = i + 14
myDate = myDate + 1

'------------------------------------'

Loop
' &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
End Sub