Copy and Paste Macro

[maninjapan]

I am using the following to copy and paste data. However if I use a second spreadsheet at the same time I get an error (I use Japanese windows so not sure of the english translation but the error code is '9'). I'm guessing I need to not only specify the sheet but also the spreadsheet itself to where the data is being copied. Any assistance here would be muchly appreciated



[VBA]Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 'seconds
Public Const cRunWhat = "TheSub" ' the name of the procedure to run
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub
Sub TheSub()

Application.ScreenUpdating = False
''''''''''''''''''''''''
With Worksheets("Data")

LastRow = .Range("C" & Rows.Count).End(xlUp).Row + 1
Worksheets("Sheet1").Range("A42:F42").Copy
.Range("C" & LastRow).PasteSpecial Paste:=xlPasteValues

End With

''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub
[/VBA]

[Kenneth Hobs]

Yes.

[maninjapan]

So I believe the specific line that I need to fix is

[VBA] With Worksheets("Data")[/VBA]

Im having trouble getting the correct format to specify the workbook and hopefully solve the error.

What would be the correct format to write that?

[Rob342]

That all depends whether the workbook you are coping from is open at the same time as the current worksheet.

[frank_m]

If the destination workbook is open, then something like this should work
[vba]
Sub TheSub()
Dim LastRow As Long, Wks1 As Worksheet, Wks2 As Worksheet
Set Wks1 = ThisWorkbook.Worksheets("Sheet1")

Set Wks2 = Workbooks("OtherWorkbook.xls").Worksheets("Data")

With Wks2
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
Wks1.Range("A42:F42").Copy
.Range("C" & LastRow).PasteSpecial Paste:=xlPasteValues
End With
End Sub
[/vba]

[chensi]

Thanks for the nice article. It is very useful.


_________________________________________________________________
I live my life in colour and see in soundMake sure you are heard!

[Rob342]

maninJapan / chensi
If you want to copy data from a closed workbook into the one you have open, i use this routine, have commented it out to make it easier to understand, could be usefull for the future?
Just change the workbook names & sheets to what you want
[vba]
Option Explicit
Option Private Module
Sub UpdateDetails()
'// Update From Closed workbook
Dim wbD As Workbook ' this is where the data is coming from
Dim wbM As Workbook ' this is where the data is going to Master Copy
Dim wsM As Worksheet
Dim wsD As Worksheet
Dim FilePath As String
Dim LastRow As Long
Dim LastRowData As Long

Application.ScreenUpdating = False

FilePath = "C:\Documents and Settings\Administrator\My Documents\TestData.xls" ' Change to suit
'// Open up the closed workbook using the filepath
Workbooks.Open (FilePath)

Set wsD = Worksheets("Sheet1") 'Closed W/Book TestData.xls sheet1
Set wbM = Workbooks("MasterData.xls") ' MasterData W/Book currently open
Set wsM = wbM.Worksheets("Data") ' MasterData Sheet "Data"

LastRowData = wsD.Cells(wsD.Rows.Count, "A").End(xlUp).Row ' Closed W/Book sheet1
LastRow = wsM.Cells(wsM.Rows.Count, "A").End(xlUp).Row ' Current open W/Book Data Sheet

'// Copy the range A2 to D(lastRow)
With wsD
.Range("A2" & LastRowData).Copy
ActiveWorkbook.Close savechanges:=True 'or make it false
End With
'// Paste the data in the open workbook sheet data next avail line and save
With wsM
wsM.Cells(LastRow + 1, "A").PasteSpecial Paste:=xlPasteAll
wbM.Save
End With
'// Activate the current workbook
ThisWorkbook.Activate
Worksheets("Data").Select 'Set focus back to current w/b sheet Data
Application.ScreenUpdating = True
End Sub
[/vba]

Rob

[maninjapan]

Rob, thanks for the input but not quite the issue I have.
I am simply copying from sheet A to Sheet B in the same workbook. The problem I have is while I have this workbook running, every time I open a non-related workbook to work on I get the error and the macro stops.

I only run the macro in the workbook when it is open.

[maninjapan]

frank, thanks for the reply. I will try this when I get a chance later today

[Rob342]

maninjapan

That was not your original question or have both frank m and i misunderstood
post a copy of the Workbook with some data and exact requirements

Rob

[Kenneth Hobs]

[VBA]Sub TheSub2()

Application.ScreenUpdating = False
''''''''''''''''''''''''
With ThisWorkbook.Worksheets("Data")

LastRow = .Range("C" & Rows.Count).End(xlUp).Row + 1
ThisWorkbook.Worksheets("Sheet1").Range("A42:F42").Copy
.Range("C" & LastRow).PasteSpecial Paste:=xlPasteValues

End With

Application.ScreenUpdating = True

''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub[/VBA]

[maninjapan]

Rob, apologies for the ambiguity in my initial explanation. However what I was actually after was what I explained further on. My fault for not being clearer straight up.

Your example is not wasted however, this is something I will need a little further along. One question regarding your example though. You specifically refer to copying from a closed workbook. Will this still work if the workbook being copied to and the workbook being copied from are both open at the same time?

Thanks,

[Rob342]

maninjapan

Frank m routine will work with both workbooks open at the same time
Kenneth's routine copies from sheet1 to sheet2 in the same workbook
No it would not work if both workbooks are open at the same time in the present format, you would have to remove any reference to opening the workbook and filepath statements and set the variables to your new names.
You can use that routine to transfer how many files you want by adding an array & a loop to it.

If you need that sort of routine later then let me know.

If this solves this post can you please mark it as solved ok

Rob

[maninjapan]

Rob, thanks. All clear on that now.

Before I close this I have one small adjustment I would like to make.
The macro now does what I expect it to and opening an unrelated book does not interfere with it.

However, I currently use a button to start this macro, I have occasionally clicked it twice (by mistake) therefore doubling the cut and pasted data, is there a condition I can add to the original code to ensure it doesnt execute the macro again after it is already running?

Big thanks!!

[shrivallabha]

If you want to run it once in a session then try something like:
[VBA]Sub RunOnceInASession()
Static iRunCount As Integer
iRunCount = iRunCount + 1
If iRunCount > 1 Then MsgBox "You've already run it once!": Exit Sub
'first time run code here
End Sub
[/VBA]

[maninjapan]

Thanks for that helped me fix up my problem.

I have one more. This macro is running pretty much full time for me, copy and pasting data that I receive real time from my trading platform. However while I have it running in the background I also work on other things in word and excel. When I go to copy and paste something I often end up pasting the data that my macro has copied..... Is there any way to keep the data being copied and pasted to remain for use only within the Excel spreadsheet it is intended for?


Thanks,

[maninjapan]

I am still trying to find a a way around the above problem. (I keep getting the data pasted in my other documents that I am working on on the same PC)

This is where I am currently at code wise, I have replaced copy and paste, with Value =, however, when I run the macro, nothing happens.... can anyone see anything wrong with this macro? I have attempted to run it without the if statement to no avail, as well as double check the sheet names are correct.

Im not sure if this has anything to do with it, however the range of values that is beign copied are not directly entered in to the cell but are real time values from other cells.

[vba]Public RunWhen As Double
Public Const cRunIntervalSeconds = 10 'seconds
Public Const cRunWhat = "TheSub" ' the name of the procedure to run
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub
Sub TheSub()

Application.ScreenUpdating = False
''''''''''''''''''''''''
With ThisWorkbook.Worksheets("Sheet4")

If ThisWorkbook.Worksheets("spread").Range("L8").Value = "TRUE" Then
LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Range("C" & LastRow).Value = ThisWorkbook.Worksheets("spread").Range("L23:CD2").Value
End If

End With

''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub

[/vba]

Thanks