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
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
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>
0
Ya it works thanks alot
0