Copy data one excel sheet to other sheet [Solved/Closed]

Report
-
 Fawad -
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

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
It is not clear what you want. You want to whole sheet to be copied to another sheet it is easier. you do not need a macro

for example you original sheet is sheet 1
select any cell.
hit control+A
all the cells in the sheet1 will be selected.
right click and click "copy" in the sub mentu
go to sheet2 and select A1 and right click and click "paste"

This is a simple procedure not requiring a macro
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2812 users have said thank you to us this month

Dim 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 & ").xlsm]" & myDay & suffix & "'!$J$37"


'Sep Operating Pressure(psig-Main)

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

'Sep Operating Temprature(F-Main)

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

'Sep Gas Throughput (MMscf-main)

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


'Sep Condensate Throughput (bbls-main)


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


'Sep Water Throughput (bbls)



Range("F" & i + 5) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xlsm]" & 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 & ").xlsm]" & myDay & suffix & "'!$J$46"


'Hours online(test)

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

'Sep Operating Pressure(Test)

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

'Sep Operating Temprature(F-Test)

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

'Sep Gas Throughput (MMscf-Test)

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


'Sep Condensate Throughput (bbls-Test)


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


'Sep Water Throughput (bbls-test)



Range("F" & i + 12) = "='G:\[daily_plant_report_(" & myMonth & myYear & ").xlsm]" & 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 & ").xlsm]" & myDay & suffix & "'!$N$46"



i = i + 14
myDate = myDate + 1

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

Loop
' &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
End Sub</code>
Ya it works thanks alot