Excel Macro to take data from sheet and Open, Copy, and Clear data to another workbook on another computer.

what is the UNC path and filename you want the data sent to?  Will it be appending to existing data or creating a new sheet in that workbook? If appending, what is the sheetname?

I don't have a path yet, as the computers have not been set up, but I think if we can get it working on a file located even on the desktop to test, we can change location later.

The file attached looks identical to the second sheet from the first file I attached to question. It is the same sheet. The data needs to go both locations.
1. To the DB sheet on the main workbook.
2. And to the bottom of the daily sheet in a different workbook.

Does that make sense?
Thanks.
Chris
114-Daily-Locations-Assigned.xlsm

sure, thank you.  I will modify what you have to also put information into another workbook ... get back to you shortly ...

here you go!

Sub SendToDB()
'151116 modified by strive4peace

   On Error GoTo Proc_Err
   
   Dim wsDB As Worksheet
   Dim wbDB As Workbook
   Dim wsConsolidate As Worksheet
   Dim wbConsolidate As Workbook
   
   Dim lngNextRowDB As Long
   Dim lngNextRowConsolidate As Long
   Dim lngRowDB1 As Long
   Dim lngRowDB2 As Long
   Dim lngLastRowData As Long
   Dim lngRow As Long
   Dim sPathFile As String
   
   sPathFile = ActiveWorkbook.Path & "\ConsolidatedDB.xlsx" '--- customize this
   '                           this workbook has a sheet called: ConsolidatedDB
   
   lngNextRowDB = Sheets("DB").Cells(Rows.Count, 1).End(xlUp).Row + 1
   lngRowDB1 = lngNextRowDB
   
   Set wbDB = ActiveWorkbook
   Set wsDB = Sheets("DB")
   
   With Sheets("InputData")
        lngLastRowData = .Range("C1048576").End(xlUp).Row
       ' .Range("C4:J" & lngLastRowData).Copy Destination:=wsDB.Cells(lngNextRowDB, "A")
       For lngRow = 4 To lngLastRowData
           If .Cells(lngRow, "C") <> "" Then
               wsDB.Cells(lngNextRowDB, "A") = .Cells(lngRow, "C")
               wsDB.Cells(lngNextRowDB, "B") = .Cells(lngRow, "D")
               wsDB.Cells(lngNextRowDB, "C") = .Cells(lngRow, "E")
               wsDB.Cells(lngNextRowDB, "D") = .Cells(lngRow, "F")
               wsDB.Cells(lngNextRowDB, "E") = .Cells(lngRow, "G")
               wsDB.Cells(lngNextRowDB, "F") = .Cells(lngRow, "H")
               wsDB.Cells(lngNextRowDB, "G") = .Cells(lngRow, "J")
               lngNextRowDB = lngNextRowDB + 1
           End If
       Next
   End With
  lngRowDB2 = lngNextRowDB - 1
  
   'open external workbook
   Set wbConsolidate = Workbooks.Open(sPathFile)
   Set wsConsolidate = wbConsolidate.Sheets("ConsolidatedDB")
   wsConsolidate.Select
   lngNextRowConsolidate = wsConsolidate.Cells(Rows.Count, 1).End(xlUp).Row + 1
   
   'copy data from the DB sheet that was just added
   wbDB.Activate
   wsDB.Select
    With wsDB
       Application.CutCopyMode = False
      .Range("A" & lngRowDB1 & ":G" & lngRowDB2).Copy
    End With
   
   'switch to the consolidated workbook and paste
   wbConsolidate.Activate
   wsConsolidate.Cells(lngNextRowConsolidate, 1).Select
   ActiveSheet.Paste
   
   'close the other workbook and save the changes
   wbConsolidate.Close True
   
   'delete data on the input sheet
   With Sheets("InputData")
      .Select
      .Range(.Cells(4, 1), .Cells(4, 1).SpecialCells(xlLastCell)).ClearContents
      .Cells(4, 1).Select
   End With
      
   MsgBox "Done consolidating data", , "Done"
   
      
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set wsConsolidate = Nothing
   Set wsDB = Nothing
   Set wbDB = Nothing
   Set wbConsolidate = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SendToDB"

   Resume Proc_Exit
   Resume
   
End Sub

Select allOpen in new window

So I no longer need my original VB, right ? This one does it all, is that the case?
Or do i just drop this one int a new module ?
Thanks
(very NOvice VB guy)
Chris

Hi Chris,

you're welcome

yes, you would replace your original code .  Be sure the FIRST line of the module is:

Option Explicit

Select allOpen in new window

I should have included that for you ~

> (very NOvice VB guy)

The code I modified looked so good that I didn't comment it ... guess someone else wrote that for you?  I would have added comments to that too if I would have realized ...

~~~~~~~~~ Compile ~~~~~~~~~
 
Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing.
 
from the menu in a VBE (module) window: Debug, Compile
(Alt-F11 to switch to the code window)
 
Fix any errors on the yellow highlighted lines.
Add needed references and remove missing references if necessary
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save

also be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up

_______________
For better understanding of Visual Basic for Applications, read the 3 chapters (all I wrote) here:

Learn VBA
http://www.AccessMVP.com/strive4peace/VBA.htm

They won't take that long -- and I think you will find them interesting.  Chapters 2 and 3 are for reference as opposed to really reading so you can just skim them.

Correct I did not write the code.
I will play with what you have for me and try to make it work.
Thanks
I will follow up on this thread if I still an unable to make it work.
Chris

Hmmm,
I am doing something wrong....
I am getting a out of Range error (9)

Was I supposed to do something with that Range?

  ' .Range("C4:J" & lngLastRowData).Copy Destination:=wsDB.Cells(lngNextRowDB, "A")

I have both files in one folder for now on my desktop. The main file is 114CaptureProgram.xlsm
And the Consolidate file is ConsolidatedDB.xlsm

Thanks so much.
ConsolidatedDB.xlsm
114CaptureProgram.xlsm

the code assumed it would be IN the workbook you were running from ... and that workbook is active when you start it

the line you pasted is commented

the code also assumed that the Consolidated workbook would have a sheet called: ConsolidatedDB, which it does not -- and that is probably why you are getting an error.

This can be changed to something else, but it should be specified.  Search for this in the code and change it as you like.  I hesitate to just use Sheet1 since someone may add a chart or something and it might no longer be the first sheet.  Also, someone may see "Sheet1" and decide to give it a better name.

to rename a sheet, double-click on the sheet tab name -- and best to copy and paste! (ctrl-C, ctrl-V)

Let me know if this solves it!

Hi Crystal,
I have started reading your PDf's it's great. It's a slow read for me. But I am determined. Just never have streams of time. I am working on this project from work, with a lot of other NON VB projects.

I have tried to re-allign the file names, and workbooks names to work with the code. But I am still missing something.  

Right now the VB is inside 114CaptureProgram which has a tab called DB.
In this same folder (which WILL be on a different computer once we get the hardware all sorted out) the Consolidate.xlms file has only one tab ConsolidatedDB.xlms which is ready for the data.

What did I do wrong??
Thanks  Crystal

Chris :(
114CaptureProgram.xlsm
Consolidate.xlsm

Hi Chris,

thank you , happy you like Access Basics :)

When you run the code, an error appears.  Press Ctrl-Break and then choose Debug.  This will take you to the error handler code.  (you may have to OK the message first)

right-click on the statement at the bottom that says Resume
and choose -- Set Next Statement

press F8 to execute and go back to the bad line
--------------------------

so, it stops because it is looking for a sheet called InputData.  

Your first sheet is called Sheet1. Double-click on a sheet tab to rename it.

I am sorry I did not make that clear.  I should have put a comment in the code.
______________________

Then an error happens because it cannot find the consolidation file.  Again, Ctrl-Break, choose Debug, Set Next Statement to Resume, press F8, and the error happens  on:
Set wbConsolidate = Workbooks.Open(sPathFile)

this is not actually the statement with the problem.  The problem is  sPathFile so let's see where it is being assigned a value.

it is in this statement:
sPathFile = ActiveWorkbook.Path & "C:\Users\pike\Desktop\114Database\Consolidate.xlsm" '--- customize this

since you are specifying the full path, you do not want the path of the code workbook first, ActiveWorkbook.Path.  This statement should just be:

sPathFile = "C:\Users\pike\Desktop\114Database\Consolidate.xlsm" '--- customize this

Select allOpen in new window

____________________________
after you change the code, choose Run, Reset from the menu
then Debug, Compile
Save, and run again

even though I told you what to fix, use this method to fix things.  Fix the first problem then run again and go fix the second one ~
____________________________

once you fix these 2 things, you should be good to go!

Excellent, Your instructions are awesome. I can pretty much follow everything. Doesn't all make complete sense, but I think I am getting what you say. I made the changes and it works great.

Here is the sticky part. Your code you created for me was a second part from a Expert who was unable to finish everything.  I have his working (first) part, and your working (second) part, and I need them to work together and play nice.

The first part takes a barcode input of a certain length and puts it into a certain cell C4, moves across to J4 column and waits for a shorter code and It references the DB Sheet to fill in 2 pieces of missing information to auto fill the rest of the row then drops down to the next line.. The logic , the code works great of the first part.

He created a Transfer and reset button, but that's where we ended. I created the second button, and was able to make it go to a second tab. Then I created a new workbook without his code to ask for help on thinking I could just add the second part to it and it would work. Hmmm , Not so much.

His code is a lot trickier, as it locks the sheet so the barcode scanner can only scan into cells that are specified. But it is easy to unlock with password "Scan"

I renamed the Primary sheet back to scan and changed your code from InputData to scan as his code might be trickier to change.

What do you think, is this workable? After OUR button 2 is working and it goes back to the beginning of his script we should be 100% functional.

Eagerly awaiting your response. Thank you so much.
Chris
114CaptureProgram.xlsm
114-Warehouse-Scan-2-.xlsm

thank you Chris.  Our internet went out this morning due to high winds -- let me catch up and then I will look at what you sent.

A storm past through yesturday for us and it by-passed most of us.
Good luck, and thanks.
Chris

Hi Chris,

I didn't do the colors and think I got the protection stuff you need into the code I wrote.  I also reassigned the button macro.  See if this is what you need.
114-Warehouse-Scan-151118.xlsm

oh! Rename the sheet to
InputData

I didn't change the code to be Scan -- you can do that too, if you want to keep your sheetname as it is ...

Thanks,
I changed the name of the main sheet to InputData

I did a FIND for everything that had SCAN in the code.
I know where I see wsScan I need to change, but what about  arScan? Should I change all those too?
Thanks
Sunny here in VAncouver today, Little or no Rain

wsScan and arScan  are just variable names, they can stay the same

in the Workbook_Open event, change
Set ws = Worksheets("Scan")
to
Set ws = Worksheets("InputData")
OR
in the mod_SendToDB module, change
With Sheets("InputData")
to
With Sheets("Scan")
(that will be in 2 places)

it is sunny here too -- and we have snow on the ground.  My backyard is a big messy though because my shelves with gardening stuff blew down!

OK i will try again, Thanks
HMmm when I try to open it, i get this error
After I finish the edit will this fix this as well??
Thanks
Chris

in that case, I would suggest just commenting that line ... use the method I told you to break the code and go to the line with the problem :)  Then, use an Up or Down arrow key to get off the line, Debug, Compile, Save, and press F5 to keep on running!

To comment a line, add a single quote ' to the beginning

That line was copied from what you gave me and, quite frankly, I don't even know why it was in there.  Locked cells should be set up on the sheet already, and then enforced when the sheet is protected, which is done by the code too.

OK great we are getting somewhere.
As soon as I open it errors out. Like this.

When I de-bug i get this


After that it seems to work, other than, every time I dump and send the data, the active cell stays in the last position after clear and The skid Number column seems to disappear too.

We need the program to go back to cell C4 and wait for the next Long Code.

It's weird how when click end or debug then close, the error goes away and it seems to work (other than resetting the cell placement)

I am learning...
Thanks so much...
Chris
114-Warehouse-Scan-151118-1-.xlsm

that error is happening in the Workbook_Open event.  This looks like more half-baked code to me, sorry to say.  In the first row, D1 is a merged cell, which is why it gets an error.

Looks like the code intended to loop

But honestly, I think if you manually set Locked in the the columns you might want to change to be not checked, all that Locked stuff can be commented! ... or deleted!

I think I get what you are saying.
The gentleman who wrote the code, well WE, decided it was best it the person who had the sheet open, could not select any cells at all, the program would go to the correct spot, wait for a certain length of code, (long code first), then move to the next "J" field and wait for the shorter code (short Code).

Another element of his code fills in the missing blanks of the code coming from the barcode scanner by looking into the DB sheet for the item number then it puts in the Flavor name and the case count unless the code has a case count already in it.  The code will have 4 or 5 parts. If 5 are there then do not look at DB just fill in cell.

1185474 05OCT15 J1234 25 (NO CASES COUNT BECAUSE FULL SKID - WILL READ FROM DATA SHEET)
1185474 05OCT15 J1234 26 15 (THIS PALLET HAS ONLY 15 LEFT OVER CASES- DO NOT LOOK AT DATA SHEET)

This is useful as it will be a forklift driver operating a hand held scanner, and a Tablet pc will be mounted to the forklift. We didn't want them to have any issues with clicking cells. One button when they complete a series of scans is ok, but we don't want them accidentally selecting wrong cells.

That being said.

I have no idea how to change his code to work with yours, or yours to work with his.
I need your help on this for sure... Would it be easier to re-write the first part, based on the above info?
Thank You
Chris

Above and beyond help on this question.
Trying to join two coders code into one code is tough, Crystal did awesome.

I added a new question, it's kind of vague. But if its you helping then awesome. :)
I am off work now and heading home for the night.
Thanks so much for your help
Chat soon.
Chirs

thank you, Chris

I am looking ~