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

Hi Experts,
I have a workbook that has a Macro that sends data to a new sheet in the same workbook from a button. That's great, now I would like the same button to also open another workbook and send the same data to it. It will be a the exact same data, just on a different computer.

After the data is sent to both locations, I need it to clear the main originating data out, so it is ready to accept new data and start again.

chris pikeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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?
chris pikeAuthor Commented:
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?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
sure, thank you.  I will modify what you have to also put information into another workbook ... get back to you shortly ...
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
   End With
  lngRowDB2 = lngNextRowDB - 1
   'open external workbook
   Set wbConsolidate = Workbooks.Open(sPathFile)
   Set wsConsolidate = wbConsolidate.Sheets("ConsolidatedDB")
   lngNextRowConsolidate = wsConsolidate.Cells(Rows.Count, 1).End(xlUp).Row + 1
   'copy data from the DB sheet that was just added
    With wsDB
       Application.CutCopyMode = False
      .Range("A" & lngRowDB1 & ":G" & lngRowDB2).Copy
    End With
   'switch to the consolidated workbook and paste
   wsConsolidate.Cells(lngNextRowConsolidate, 1).Select
   'close the other workbook and save the changes
   wbConsolidate.Close True
   'delete data on the input sheet
   With Sheets("InputData")
      .Range(.Cells(4, 1), .Cells(4, 1).SpecialCells(xlLastCell)).ClearContents
      .Cells(4, 1).Select
   End With
   MsgBox "Done consolidating data", , "Done"
   On Error Resume Next
   'release object variables
   Set wsConsolidate = Nothing
   Set wsDB = Nothing
   Set wbDB = Nothing
   Set wbConsolidate = Nothing
   Exit Sub
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SendToDB"

   Resume Proc_Exit
End Sub

Open in new window

chris pikeAuthor Commented:
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 ?
(very NOvice VB guy)
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

you're welcome

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

Option Explicit

Open 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

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.
chris pikeAuthor Commented:
Correct I did not write the code.
I will play with what you have for me and try to make it work.
I will follow up on this thread if I still an unable to make it work.
chris pikeAuthor Commented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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!
chris pikeAuthor Commented:
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 :(
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

Open 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!
chris pikeAuthor Commented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
chris pikeAuthor Commented:
A storm past through yesturday for us and it by-passed most of us.
Good luck, and thanks.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oh! Rename the sheet to

I didn't change the code to be Scan -- you can do that too, if you want to keep your sheetname as it is ...
chris pikeAuthor Commented:
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?
Sunny here in VAncouver today, Little or no Rain
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
wsScan and arScan  are just variable names, they can stay the same

in the Workbook_Open event, change
Set ws = Worksheets("Scan")
Set ws = Worksheets("InputData")
in the mod_SendToDB module, change
With Sheets("InputData")
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!
chris pikeAuthor Commented:
OK i will try again, Thanks
HMmm when I try to open it, i get this error
error-scan-nov18.JPG After I finish the edit will this fix this as well??
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
chris pikeAuthor Commented:
OK great we are getting somewhere.
As soon as I open it errors out. Like this.

error-0931.JPGWhen 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...
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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!
chris pikeAuthor Commented:
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.


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
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

this is really getting into another question ... perhaps mark this thread as answered and post another one?  Put the link here and I will look too :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chris pikeAuthor Commented:
Above and beyond help on this question.
Trying to join two coders code into one code is tough, Crystal did awesome.
chris pikeAuthor Commented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Chris

I am looking ~
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.