code not working

[Simmo-81]

hi all im having some problems with the following code.

My code is supposed to copy a variable amount of data from columns a:c to two locations. The first location is the priority.

I want the code to copy the data from sheet 1 in one workbook and copy it to a datastore page in another workbook. The reason for this is i have multiple workbooks that all need to transfer data to the first availabe row in a data store sheet.

The second part copies the same data to a sheet in the same workbook. My idea is to have a local store in the workbook to save what has been sent .

Any help on this would be really appreciated, the main thing is copying to another worksheet in another workbook.

Thanks



Sub Xfer()
Application.ScreenUpdating = False
Dim a, b, d, e As Long

a = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
b = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
d = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
e = Sheets("DataStore").Range("A" & Rows.Count).End(xlUp).Row

Set NextWB = Workbooks.Open("C:\Desktop\TestSheet2")
Sheet1.Range("A2:C" & d).Copy Destination:=NextWB.Sheets("DataStore").Range("A" & e + 1)
NextWB.Close savechanges:=True
Sheet1.Range("A2:C" & a).Copy Destination:=Sheet2.Range("A" & b + 1)
End Sub

[Bob Phillips]

You can't use a workbook before you open it



Application.ScreenUpdating = False
Dim a, b, d, e As Long

a = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
b = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
d = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Set NextWB = Workbooks.Open("C:\Desktop\TestSheet2")
e = NextWB.Sheets("DataStore").Range("A" & Rows.Count).End(xlUp).Row

Sheet1.Range("A2:C" & d).Copy _
Destination:=NextWB.Sheets("DataStore").Range("A" & e + 1)
NextWB.Close savechanges:=True
Sheet1.Range("A2:C" & a).Copy _
Destination:=Sheet2.Range("A" & b + 1)

[Simmo-81]

thanks a lot, working fine now :)

was so close to getting i right but after 21 hours of working on spreadsheets just couldnt see the answer :)

[mdmackillop]

It's clearer to set references; also declare all variables separately.
Option Explicit
Sub Xfer()
Application.ScreenUpdating = False
Dim a As Long, b As Long, d As Long, e As Long
Dim NextWB As Workbook
Dim WB As Workbook


Set WB = ThisWorkbook

a = WB.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
b = WB.Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
d = WB.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

'Open new book; get target row
Set NextWB = Workbooks.Open("C:\Desktop\TestSheet2.xls")
e = NextWB.Sheets("DataStore").Range("A" & Rows.Count).End(xlUp).Row

'Copy from source to target; close target
WB.Sheets(1).Range("A2:C" & d).Copy Destination:=NextWB.Sheets("DataStore").Range("A" & e + 1)
NextWB.Close savechanges:=True

'Make copy of data in original book
WB.Sheets(1).Range("A2:C" & a).Copy Destination:=WB.Sheet(2).Range("A" & b + 1)

Application.ScreenUpdating = True

End Sub

[Paul_Hossler]

You can't use a workbook before you open it



Actually, if you go back to Excel 4 macros, you can

Here's one way to retreive data. I don't think it's 100% general purpose, since AFAIK it only retreives cell values,


'http://exceltip.com/st/Read_information_from_a_closed_workbook_using_VBA_in_Microsoft_Excel/473.html
'This method has some limitations on how many cells you can return information from since the Excel4-macro creates
'links to the closed workbook. You can use a similar example using ADO if you need to retrieve a lot of data from
'a closed workbook.
'It is often much easier to open the workbook and get the information from it. If you set the Application.
'ScreenUpdating to False, the user will probably not notice that the workbook is opened and closed again
Option Explicit
Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String

GetInfoFromClosedFile = ""

If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"

If Dir(wbPath & wbName) = "" Then Exit Function

arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

On Error Resume Next

GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
Sub TestClosedWB()
Dim v As Variant
v = GetInfoFromClosedFile("C:\Test", "ClosedWorkbook.xlsx", "Sheet1", "A1")
MsgBox v
End Sub



Paul

[Bob Phillips]

Actually, if you go back to Excel 4 macros, you can

In the context of the code presented my statement was 100% accurate. The OP wasn't asking how to read from a closed workbook, he was opening it but trying to use it before having done so.