Simmo-81
02-14-2009, 02:03 AM
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
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