Need to automatically transfer data from survey sheet to paste sheet.

hey smuzoen, thats awesome!!! but one problem, when i enter the survey on the survey sheet it sends the data to the paste sheet as planned, but on a different row. i would like it to replace the data in row 3(cells C3 thru K3) everytime. also when i enter survey after survey on the survey sheet the macro sends it to the paste sheet and keep all the surveys enter in a running talley. i would like to only have the last entered survey on the Paste sheet and only the last instead of the running talley. i really appreciate the time and effort your putting in. thank you

thanks a million Anthony. i hate to bother you again, i have two sheets on the paste workbook now and how hard would it be to set both of them to automatically update. and what about adding another workbook to do the same function. do i use Set wBook = Workbooks (Calculated.xlsx"). thats the other workbook i want to auto update.

the sheets i have named them Directional Only and Gamma String. ive tried to enter them in the code like this ("Directional Only")(Gamma String") but its not workin. i think its because the format im using. whats the proper way to put it in.

hey i appreciate all that uve done for me. i got it up and going. made my job a whole lot easier. thanks again

hey anthony, i just received another email from the big wigs of my office. they added another workbook(CO-120030 Surveys), but the lucky thing is it is the same as the calculated survey workbook, but the difference is im going to be putting my info in that workbook(CO-120030 Surveys) first then to the calculated then to the Paste workbook with the two sheets(directional only, and gamma string). so i need to put the macro in the CO-120030 workbook so it transfers to the calculated workbook then to the Paste workbook. here is the code u sent me. can u alter it so i enter the data on the CO-120030 workbook and have it automatically sends to the Calculated workbook then to the Paste workbook.
i updated the post

ill be working on it later on in the night but ill let u know how its going. thanks for everything

hey there Anthony, the code worx pretty darn good, except one problem. the workbook jsoto.xlsx with the two sheets (gamma string) and (directional only), the data automatically transfers to the gamma sheet but not the directional sheet. i looked at the code but cant see any problems.

Sub justinsoto()
Dim ws1 As Worksheet
Set ws1 = Worksheets("Survey Sheet")
ws1.Range("A" & ws1.Cells(Rows.Count, "B").End(xlUp).Row & ":I" & ws1.Cells(Rows.Count, "B").End(xlUp).Row).Copy
On Error Resume Next
Set wBook = Workbooks("jsoto.xlsx")
If wBook Is Nothing Then
Workbooks.Open Filename:="C:\Users\Robert\Desktop\jsoto.xlsx"
Worksheets("Gamma String").Range("C" & Cells(Rows.Count, "C").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Directional Only").Range("C" & Cells(Rows.Count, "C").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set wBook = Nothing
On Error GoTo 0
Else
Windows("jsoto.xlsx").Activate
Worksheets("Gamma String").Range("C" & Worksheets("Gamma String").Cells(Rows.Count, "C").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Directional Only").Range("C" & Worksheets("Directional Only").Cells(Rows.Count, "C").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
ws1.Range("A" & ws1.Cells(Rows.Count, "B").End(xlUp).Row & ":I" & ws1.Cells(Rows.Count, "B").End(xlUp).Row).Copy
On Error Resume Next
Set wBook = Workbooks("Whitfield_West_Unit_#1H_Calculated Surveys.xls")
If wBook Is Nothing Then
Workbooks.Open Filename:="G:\CO-120030\Survey's Folder\Whitfield_West_Unit_#1H_Calculated Surveys.xls" ' or location of Calculated.xls
Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set wBook = Nothing
On Error GoTo 0
Else
Windows("Whitfield_West_Unit_#1H_Calculated Surveys.xls").Activate
Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

Please Login or Register  to view this content.

The main reason why this will fail is if there is any misspelling or spaces in the sheet name - there is nothing wrong with the code. For sake of argument change the Directional Only sheet to say "abc" and change the code to reflect the name change and run it again - I am sure it will be a matter of spelling. You could use the index number of the sheet so make it

Please Login or Register  to view this content.

Where 2 is the index number of the sheet - in the top left hand corner of the VB editor you will see the sheet number

PS: Make sure you post your code with code tags

I got it, finally!!! Thanks for all your help Anthony. I appreciate it. Again thanks