Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Advanced copy and replace text between 2 multiple sheets

0

Hi!

I'm all new to VBA so I barely understand anything. I'll try to explain as best as i can. 

I am renting out lifts and I need to have an overview over their current addresse and how many parts are rented out etc. I have made a VBA macro which activates when i push the button 'Generer'. It saves Sheet1 as pdf in a designated folder on my computer and it opens an email with the document attached I can send to the customer and the warehouse for packing.

However what I'm struggling with is that I want the new address in sheet1 to overwrite the current address on sheet2 when I'm done and push generate. I have a list of different lift types and serialnumbers. So I want the address i change in sheet1 to overwrite the address in sheet2 for in the row of the correct serialnumber.

I have attached my excel workbook and a poorly drawn description in paint. I'm really not sure if this explains anything, but any help at all would be great!

Answer
Discuss

Discussion

Please attach your workbook- you forgot to do that!

Also pleas enot that you can't paste images in this forum so we won't see your "..drwn description in Paint" unless you first paste it into an Excel file and upload that too. 
John_Ru (rep: 6092) Nov 30, '20 at 7:54 am
The guy who invented the database dign't know of the word "overwrite". To him, anything you write down is worth conserving. Therefore I recommend you change your concept. If you have a DB with date-in and date-out columns, serial number, description and address, you would have a record of where your equipment was at any time. It's currently at the address of the most recent entry where it wasn't checked out from.
That's the DB you only add to, never overwrite. Everything else is grouped around it, such as a worksheet where you can enter a serial number and get the current location.
Variatus (rep: 4889) Nov 30, '20 at 6:49 pm
I couldn't add attachements to the discussion so I uploaded it as an answer!
LordBrenden (rep: 10) Dec 1, '20 at 1:07 am
Thanks for the answer Variatus. I'll take that to consideration! :) I just started trying out VBA last month and it's really interresting!
LordBrenden (rep: 10) Dec 1, '20 at 8:17 am
Add to Discussion

Answers

0
Selected Answer

Please see the modified file (attached) in which I've added the code in bold below to your Sub Email_Sheet (which runs when the button "Generer" is pressed on sheet "Pakkeliste"). 

The first two bold lines set a range to a part of the serial number column in the second tab. Then there's a loop (starting For Each Cell and ending Next Cell) where the contents of each cell are tested by an If Then ... End If loop. If a match is found, the line 

Cell.Offset(0, 2) = ActiveSheet.Range("D9"
means the cell offset by 0 rows and 2 columns (to the right) of the matching cell is set to the D9 from your packing list sheet "Pakkeliste".

Before that (to save the old data, crudely and as @Variatus pointed out the importance of keeping data), the line Cell.Offset(0, 8) = "Tidligere @ " & Cell.Offset(0, 2) & "; " & Cell.Offset(0, 8) adds the current location address to the existing conents of your Kommentar column on the same row. 

Sub Email_Sheet_Click()
Dim objOutlook As Object
    Dim objMail As Object
    Dim signature As String
    Dim oWB As Workbook
    Dim PDF_Filename As String
    Dim pakkeListe As Range
    Set oWB = ActiveWorkbook
    Dim SerNum As Range

    Set SerNum = Worksheets("Alleheiserriktig").Range("B4:B100")

    For Each Cell In SerNum

        If Cell.Value = ActiveSheet.Range("B10").Value Then
            Cell.Offset(0, 8) = "Tidligere @ " & Cell.Offset(0, 2) & "; " & Cell.Offset(0, 8) ' + adresse>  Kommentar
            Cell.Offset(0, 2) = ActiveSheet.Range("D9") ' > addresse

        End If

        Next Cell

    Set pakkeListe = Range("A4:B49")

Hope this helps.(If so please mark this Answer as Selected.)

Discuss

Discussion

This was great, thanks! I didn't even think of saving the old location in the commentary field, so that's awesome. This was probably not that advanced for you it seems, you'll probably hear from me again. And again, thank you so much!

Edit: When I'm converting to PDF and attach it to a mail all blank spaces between words become %20. Is there any way around this? I tried reading, but I couldn't find anything that made sense.
LordBrenden (rep: 10) Dec 1, '20 at 8:10 am
Brenden (m' lud?)

I can't answer you at present since my installation of Office decided to update itself.

In testing your macro, I stored the converted pdfs locally and got the name  "Test gata 2,Geda1500kg,1701902908.pdf" for example.

When you store items on the web, spaces are often removed from their URL addresses. That's because some chracters  like spaces are considered unsafe (can get mistaken when poassed through various aplications) so a space gets encoded to %20 (where 20 is the hexadecimal number for the "space" character under ASCII). See URL Encoding

Kindly note that normally any follow-on questions should be raised as fresh questions on the forum.
John_Ru (rep: 6092) Dec 1, '20 at 9:35 am
For a quick solution (giving an emailled pdf with name including spaces), why not store your pdf both locally (and on your SharePoint), attaching the local copy to your email? 
John_Ru (rep: 6092) Dec 2, '20 at 4:41 am
I did a quick read at the URL encoding page and it seems I can't have spaces. Unless it in some way is possible to first export the file to the designated folder, and then add it to the email after it is exported. I guess that would require some other kind of programming. The quick solution works for me, but I am doing hundreds of these packing lists for the warehouse every month, so I'd rather not do it. The guys at the warehouse will just have to cope with the %20's. Thanks!
LordBrenden (rep: 10) Jan 13, '21 at 7:18 am
Brenden. I've moved on from this solution but feel free to ask a new, separate question to see if someone has a better solution for the "guys at the warehouse". I may have time to think about it tomorrow.
John_Ru (rep: 6092) Jan 13, '21 at 7:41 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login