Hope you VB wizards can help here -- should've took VB in school

Searching a column for 1st no data cell(or row) - then pasting

Still nothing...

Re: Searching a column for 1st no data cell(or row) - then pasting

Please answer a few questions so we can know what your situation is:

(1) Is the information in all those employee files going to the same file, that file called Shopper.xls?

(2) What determines what sheet (per your reference to Sheet1, Sheet2, Sheet3, etc) in Shopper.xls that a particular user's information will go?

(3) Are your 5 cells vertical (A1:A5), or horizontal (A1:E5), or non-contiguous, or what?

(4) Under what circumstances do you want this information to be transferred? In other words, when should the copy and paste take place...when the user workbook closes, when they click a button, when they enter something particular in a cell (if so what cell or what row or what column), or what?

Tom Urtis

Answers to your questions...

--yes, everyone of the files the employees uses will send this information to that single file(that file will be receiving info from all the individual workbook files)

--I was vague in explaning this part. I will only use the first sheet(the other 2 of course are there as default)Essentially, it's just a running tally of info gathered of many different contacts.
--The cells are contiguous,they are horizontal
anywhere from A12:E12 through A54:E54 -- this just depends on how many contacts they have had & where they are on their sheet.
--It would be better if each record was sent together. After the last cell is entered(the one in column E actually) would be best, but actually I don't think it will really matter as long as it is updated pretty often. Also, this wont effect regular copying & pasting will it?

Thanks Tom for your attention -- this system is working my nerves at this point. Tom Urtis

Answers to your questions...

--yes, everyone of the files the employees uses will send this information to that single file(that file will be receiving info from all the individual workbook files)

--I was vague in explaning this part. I will only use the first sheet(the other 2 of course are there as default)Essentially, it's just a running tally of info gathered of many different contacts.
--The cells are contiguous,they are horizontal
anywhere from A12:E12 through A54:E54 -- this just depends on how many contacts they have had & where they are on their sheet.
--It would be better if each record was sent together. After the last cell is entered(the one in column E actually) would be best, but actually I don't think it will really matter as long as it is updated pretty often. Also, this wont effect regular copying & pasting will it?

Thanks Tom for your attention -- this system is working my nerves at this point. Tom Urtis

Re: Answers to your questions...

See if this macro does what you want.

It brings up an input box for the user to choose which row, between 12 and 54, that holds the records they want to transfer to Shopper.xls.

The code is not "purist", in that I added a few extra lines on purpose so you can see why the code does what it does. Also, I inserted a bunch of notes to explain the procedure along the way, in case you want to modify it.

Insert this macro into the users' files and attach it to a button that you can draw on the worksheet of the users' files. This way, the user is in control of when to transfer a record, and which record they want to specifically transfer.

Sub TransferRecords()
On Error GoTo a
Dim Ans As Double
Ans = Application.InputBox("Enter row number between 12 - 54", "Which row number do you want to transfer?")
Select Case Ans
Case 12 To 54
MsgBox "Click OK to transfer row " & Ans, vbInformation, "Go for it!!"
Application.ScreenUpdating = False
Range("E" & Ans).Activate
Range(ActiveCell, ActiveCell.Offset(, -4)).Copy
'See if Shopper.xls is open, if not, open it
On Error GoTo b:
Windows("Shopper.xls").Activate
'If the file is not open there will be an error and we GoTo b:
GoTo c:
b:
ChDir "C:\Your\File\Path"
Workbooks.Open Filename:="C:\Your\File\Path\Shopper.xls"
'This will open the Shopper.xls file
c:
'Now when we get to c: the file is open either way,
'so we activate it:
Windows("Shopper.xls").Activate
Sheets("Sheet1").Select
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
'Now save and close Shopper.xls because we don't need it anymore
ActiveWorkbook.Save
ActiveWorkbook.Close
'Clear clipboard
Application.CutCopyMode = False
Application.ScreenUpdating = True

Case 0
MsgBox "You cancelled or " & Ans & " is not in the Valid range", vbExclamation, "Invalid entry"
Exit Sub
Case Else
MsgBox Ans & " is not in the Valid range", vbExclamation, "Invalid entry"
Exit Sub

a:
Exit Sub
End Select

End Sub

Thanks to someone on this board (can't remember who) who gave me the inspiration for the input box interface some months ago.

HTH

Tom Urtis

--yes, everyone of the files the employees uses will send this information to that single file(that file will be receiving info from all the individual workbook files) : (2) What determines what sheet (per your reference to Sheet1, Sheet2, Sheet3, etc) in Shopper.xls that a particular user's information will go? --I was vague in explaning this part. I will only use the first sheet(the other 2 of course are there as default)Essentially, it's just a running tally of info gathered of many different contacts. : (3) Are your 5 cells vertical (A1:A5), or horizontal (A1:E5), or non-contiguous, or what? --The cells are contiguous,they are horizontal anywhere from A12:E12 through A54:E54 -- this just depends on how many contacts they have had & where they are on their sheet. : (4) Under what circumstances do you want this information to be transferred? In other words, when should the copy and paste take place...when the user workbook closes, when they click a button, when they enter something particular in a cell (if so what cell or what row or what column), or what? --It would be better if each record was sent together. After the last cell is entered(the one in column E actually) would be best, but actually I don't think it will really matter as long as it is updated pretty often. Also, this wont effect regular copying & pasting will it? Thanks Tom for your attention -- this system is working my nerves at this point. : : Tom Urtis

I can't seem to get this to work...

It brings up an input box for the user to choose which row, between 12 and 54, that holds the records they want to transfer to Shopper.xls. The code is not "purist", in that I added a few extra lines on purpose so you can see why the code does what it does. Also, I inserted a bunch of notes to explain the procedure along the way, in case you want to modify it. Insert this macro into the users' files and attach it to a button that you can draw on the worksheet of the users' files. This way, the user is in control of when to transfer a record, and which record they want to specifically transfer. Sub TransferRecords() On Error GoTo a Dim Ans As Double Ans = Application.InputBox("Enter row number between 12 - 54", "Which row number do you want to transfer?") Select Case Ans Case 12 To 54 MsgBox "Click OK to transfer row " & Ans, vbInformation, "Go for it!!" Application.ScreenUpdating = False Range("E" & Ans).Activate Range(ActiveCell, ActiveCell.Offset(, -4)).Copy 'See if Shopper.xls is open, if not, open it On Error GoTo b: Windows("Shopper.xls").Activate 'If the file is not open there will be an error and we GoTo b: GoTo c: b: ChDir "C:\Your\File\Path" Workbooks.Open Filename:="C:\Your\File\Path\Shopper.xls" 'This will open the Shopper.xls file c: 'Now when we get to c: the file is open either way, 'so we activate it: Windows("Shopper.xls").Activate Sheets("Sheet1").Select Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll) 'Now save and close Shopper.xls because we don't need it anymore ActiveWorkbook.Save ActiveWorkbook.Close 'Clear clipboard Application.CutCopyMode = False Application.ScreenUpdating = True Case 0 MsgBox "You cancelled or " & Ans & " is not in the Valid range", vbExclamation, "Invalid entry" Exit Sub Case Else MsgBox Ans & " is not in the Valid range", vbExclamation, "Invalid entry" Exit Sub a: Exit Sub End Select End Sub Thanks to someone on this board (can't remember who) who gave me the inspiration for the input box interface some months ago. HTH Tom Urtis

I've worked with this code for awhile now & I still cannot get it to run properly - actually - it wont do anything. I am using a Networked System where every user has access to this one file(about 200 users) -- is this causing - or will it cause a problem with this particular code(solution)?
Thanks again,
nica

I tested it a dozen times and it worked, so let's see...

This code worked every time I tested it before posting it, so let's see,

(1) When you say it "won't do anything", did you attach it to a button on the given worksheet, or are you clicking Tools > Macro > Macros > Run, or what?

(2) Did you modify these 2 lines of code to reflect your actual drive and file path?:

ChDir "C:\Your\File\Path"
Workbooks.Open Filename:="C:\Your\File\Path\Shopper.xls"

(3) The macro does not go into the Shopper.xls file, it goes into each user file; be sure you insert it into the users' files.

If these suggestions don't help please repost so we can try to think of other reasons.

Tom Urtis

Working now - it is pasting on same sheet. See Code...

(1) When you say it "won't do anything", did you attach it to a button on the given worksheet, or are you clicking Tools > Macro > Macros > Run, or what? (2) Did you modify these 2 lines of code to reflect your actual drive and file path?: ChDir "C:\Your\File\Path" Workbooks.Open Filename:="C:\Your\File\Path\Shopper.xls" (3) The macro does not go into the Shopper.xls file, it goes into each user file; be sure you insert it into the users' files. If these suggestions don't help please repost so we can try to think of other reasons. Tom Urtis

Tom,
I have it working now - somewhat.
Of course, your code works great -- it copies the
date in the correct row & pastes the correct data.
Oly problem is that it pastes it back to same sheet, in next available row. I don't need it pasted in the same file it is being retrieved from, but the running total file on the next empty row --> see code please.
I created a simple workbook called CodeTest - where the data will be copied from, and then a simple file called Tester with just a few rows of meaningless info - where the data is to be copied to. See if you can tell what I'm doing wrong perhaps.?.?
Thanks again Tom, i can't appreciate it enough!
nica

Re: Working now - it is pasting on same sheet. See Code...

Well, again, when I tested it, it copied the record into the Shopper file as it should, which makes sense according to sequence in the code: copy record, open Shopper, activate Shopper, go to Sheet1, next available row, and then paste.

There must be a reason why this is happening to you when it did not happen to me. Try opening up the Shopper file first, then go back to a user file and try the code again. I wrote the code to open and activate the Shopper file whether it is already open on your desk top or closed.

If this idea does not work then we can explore other options.

T.U.

Same problem: what I'm doing, step-by-step:

Ok, I am adding a button using the control toolbox
Then, I am opening the design mode, right clicking on the button, going to view code, & pasting in the following code.
I am having to remove the Sub TransferRecords() so the sys can use the default Private Sub CommandButton1_Click() for the button -- which I don't think is causing the problem - I removed one of the End Sub's too so everything matches.

Here is what this code is doing:
I have 2 workbooks(specifically to get this code working - I've tried two systems)
The first workbook is called TestCode(taking the place of the user's file) located at c:\
the running totals for all users is still Shopper, but I've put it in a unique file called ExcelTrial.
The sheets on Shopper are still defaulted to Sheet1,etc(only sheet1 being used of course)
The sheet on TestCode is called 'username' - the rest are not being used. Although the sheet name is entirely different then what the code is utilizing, the program copies the row & cells(perfectly) but then searches the SAME user's sheet until the next open and pastes it there -
there is no indication that Shopper is ever being touched.

I'm sorry to keep bothering you with this - but I can't seem to work it out.

Thanks once more...
nica

**********Code I'm using*****************

Private Sub CommandButton1_Click()

'Sub TransferRecords()

On Error GoTo a
Dim Ans As Double
Ans = Application.InputBox("Enter row number between 12 - 54", "Which row number do you want to transfer?")
Select Case Ans
Case 12 To 54
MsgBox "Click OK to transfer row " & Ans, vbInformation, "Go for it!!"
Application.ScreenUpdating = False
Range("E" & Ans).Activate
Range(ActiveCell, ActiveCell.Offset(, -4)).Copy
'See if Shopper.xls is open, if not, open it
On Error GoTo b:
Windows("Shopper.xls").Activate
'If the file is not open there will be an error and we GoTo b:
GoTo c:
b:
ChDir "C:\ExcelTrial"
Workbooks.Open Filename:="C:\ExcelTrial\Shopper.xls"
'This will open the Shopper.xls file
c:
'Now when we get to c: the file is open either way,
'so we activate it:
Windows("Shopper.xls").Activate
Sheets("Sheet1").Select
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
'Now save and close Shopper.xls because we don't need it anymore
ActiveWorkbook.Save
ActiveWorkbook.Close
'Clear clipboard
Application.CutCopyMode = False
Application.ScreenUpdating = True

Case 0
MsgBox "You cancelled or " & Ans & " is not in the Valid range", vbExclamation, "Invalid entry"
Exit Sub
Case Else
MsgBox Ans & " is not in the Valid range", vbExclamation, "Invalid entry"
Exit Sub

a:
Exit Sub
End Select

'End Sub

End Sub

Problem identified, here's a solution

Thank you for the step by step detail. I was testing the code while it was attached to a button drawn off the Forms toolbar, which works. You were embedding a Command Button off the Control Toolbox toolbar, and attaching the macro to it. The problem is, ActiveX controls (such as a Command Button) have certain properties that enable them to handle events, but not necessarily any ol' macro attached to it.

Delete the Command Button, then go to View > Toolbars > Forms to bring up the Forms toolbar. I believe that the 4th object from the left on the Forms toolbar is a Button. Click on that Button icon, then draw a button from that onto your worksheet, and attach my original macro to that button. Try the original macro again by clicking on your new Forms button - - the macro should work this time. You can also attach the macro to other objects, such as shapes from the Drawing toolbar, in case you want to create a colorful button.

Tom Urtis

Ok, I am adding a button using the control toolbox Then, I am opening the design mode, right clicking on the button, going to view code, & pasting in the following code. I am having to remove the Sub TransferRecords() so the sys can use the default Private Sub CommandButton1_Click() for the button -- which I don't think is causing the problem - I removed one of the End Sub's too so everything matches. Here is what this code is doing: I have 2 workbooks(specifically to get this code working - I've tried two systems) The first workbook is called TestCode(taking the place of the user's file) located at c:\ the running totals for all users is still Shopper, but I've put it in a unique file called ExcelTrial. The sheets on Shopper are still defaulted to Sheet1,etc(only sheet1 being used of course) The sheet on TestCode is called 'username' - the rest are not being used. Although the sheet name is entirely different then what the code is utilizing, the program copies the row & cells(perfectly) but then searches the SAME user's sheet until the next open and pastes it there - there is no indication that Shopper is ever being touched. I'm sorry to keep bothering you with this - but I can't seem to work it out. Thanks once more... **********Code I'm using***************** Private Sub CommandButton1_Click() 'Sub TransferRecords() On Error GoTo a Dim Ans As Double Ans = Application.InputBox("Enter row number between 12 - 54", "Which row number do you want to transfer?") Select Case Ans Case 12 To 54 MsgBox "Click OK to transfer row " & Ans, vbInformation, "Go for it!!" Application.ScreenUpdating = False Range("E" & Ans).Activate Range(ActiveCell, ActiveCell.Offset(, -4)).Copy 'See if Shopper.xls is open, if not, open it On Error GoTo b: Windows("Shopper.xls").Activate 'If the file is not open there will be an error and we GoTo b: GoTo c: b: ChDir "C:\ExcelTrial" Workbooks.Open Filename:="C:\ExcelTrial\Shopper.xls" 'This will open the Shopper.xls file c: 'Now when we get to c: the file is open either way, 'so we activate it: Windows("Shopper.xls").Activate Sheets("Sheet1").Select Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll) 'Now save and close Shopper.xls because we don't need it anymore ActiveWorkbook.Save ActiveWorkbook.Close 'Clear clipboard Application.CutCopyMode = False Application.ScreenUpdating = True Case 0 MsgBox "You cancelled or " & Ans & " is not in the Valid range", vbExclamation, "Invalid entry" Exit Sub Case Else MsgBox Ans & " is not in the Valid range", vbExclamation, "Invalid entry" Exit Sub a: Exit Sub End Select 'End Sub End Sub

Working,but, I get a run-time error more often than not...

Delete the Command Button, then go to View > Toolbars > Forms to bring up the Forms toolbar. I believe that the 4th object from the left on the Forms toolbar is a Button. Click on that Button icon, then draw a button from that onto your worksheet, and attach my original macro to that button. Try the original macro again by clicking on your new Forms button - - the macro should work this time. You can also attach the macro to other objects, such as shapes from the Drawing toolbar, in case you want to create a colorful button. Tom Urtis

Ok, you were definately correct. It will work
now, but only a couple times. More often than not I'm getting an error:

An app. error has occured & an app. log has been generated.

Excel.exe
Exception: access violation(0xc0000005),address:
0x3010925a

I can't imagine why I would have an access violation. It's certainly not something I can't access regularly. Familiar with this at all?

We're almost there...
nica

Re: Working,but, I get a run-time error more often than not...

I'm not sure, and just thinking out loud here, it may have to do with a network configuration, or maybe the nature of the destination file (Shopper.xls), in that if it is open by someone else at the time a another user elsewhere triggers this code, and if at the time Shopper.xls is Read Only, maybe that's why, but I really don't know for sure. I'd recommend that you post that as a separate question, I might be missing something obvious, but I just don't know why you are seeing that error. The thing with macros is, when they run correctly the first time or two, as you say this did, and which it did for me, then it should always run correctly, all things remaining constant. There's got to be a reason, I just cannot say from here. Sorry, but if I think of anything I'll let you know.

Tom Urtis Ok, you were definately correct. It will work now, but only a couple times. More often than not I'm getting an error: An app. error has occured & an app. log has been generated. Excel.exe Exception: access violation(0xc0000005),address: 0x3010925a I can't imagine why I would have an access violation. It's certainly not something I can't access regularly. Familiar with this at all? We're almost there...

Re: Working,but, I get a run-time error more often than not...

thanks for your time

I have more info tom...

I just did some more checking, it turns out -- it only gives that error when it's closed -- strange thing about what you said is that I'm doing it locally on my hardrive -- I havn't networked anything YET. Does it work on yours when it's closed? It will close it after it pastes & saves the data - it's just having a hard time opening the file I reckon???
nica

PS I did post taht error to see if anyone is familiar with it.

Re: I have more info tom...

Yes it worked when I tested it with the Shopper file open and closed. I saw your error-related post, good deal, I hope someone can shed light on this, as it did not happen to me in a dozen tests each way, open & closed.

Tom U.

I just did some more checking, it turns out -- it only gives that error when it's closed -- strange thing about what you said is that I'm doing it locally on my hardrive -- I havn't networked anything YET. Does it work on yours when it's closed? It will close it after it pastes & saves the data - it's just having a hard time opening the file I reckon??? PS I did post taht error to see if anyone is familiar with it.