+ Reply to Thread
Results 1 to 11 of 11

VBA to copy and replace data according to variable

  1. #1
    Registered User
    Join Date
    01-02-2022
    Location
    Portugal
    MS-Off Ver
    Office 2019
    Posts
    8

    VBA to copy and replace data according to variable

    Hi
    I'm an apprentice in VBA so I need your help.
    I'm building a Workbook, from which I attach a small example. The data is fictitious, but the layout is similar. What I need is a MACRO to select the sheet and file identified in the UPDATE DATA, copy the new fields and replace them in the sheet and file identified. It can copy and replace just the changed fields, or it can copy and replace all fields. I know how to identify the sheet and file ( it's an archaic way, I know, but I don't know how to do it better ). My problem is how can I replace the old data by the new data in the same sheet and file.
    Sorry for English, it's not my native language.
    Any help would be greatly appreciated.

    Thanks
    Attached Files Attached Files
    Last edited by Micro Boring; 01-02-2022 at 08:29 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: VBA to copy and replace data according to variable

    Sheet data cell A5 & A6 are helper range to combine "record" & A3, I deleted them and use cell A3 directly. Similar to A13 & A14
    For update, as I can see, use record# in A11, file# in B11 and new color in F11 is enough information to add the update color
    Other cells, ranges, if they were helper, just delete them.

    PHP Code: 
    Option Explicit
    Dim Lr
    &
    Dim wsDes As Worksheet 'wsData As Worksheet,
    Sub add()
    With Sheets("data")
        Set wsDes = Sheets("record" & .Range("A3").Text)
        Lr = wsDes.Cells(Rows.Count, "A").End(xlUp).Row
        wsDes.Range("A1:G1").Offset(Lr).Value = .Range("A3:G3").Value
    End With
    End Sub
    Sub update()
    Dim f As Range
    With Sheets("data")
        If .Range("F11") = "" Then
            MsgBox "choose new color"
            Exit Sub
        End If
            Lr = wsDes.Cells(Rows.Count, "A").End(xlUp).Row
            Set wsDes = Sheets("record" & .Range("A11").Text)
            Set f = wsDes.Range("B3:B" & Lr).Find(.Range("B11"))
                If f Is Nothing Then
                    MsgBox "file# is not exists"
                    Exit Sub
                ElseIf f.Offset(, 3) = .Range("F11") Then
                    MsgBox "pick another color"
                    Exit Sub
                End If
                f.Offset(, 3).Value = .Range("F11").Value
    End With
    End Sub 
    Attached Files Attached Files
    Quang PT

  3. #3
    Registered User
    Join Date
    01-02-2022
    Location
    Portugal
    MS-Off Ver
    Office 2019
    Posts
    8

    Re: VBA to copy and replace data according to variable

    Thanks for your support, but it's not working.
    I attach the error. Attachment 761817

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: VBA to copy and replace data according to variable

    Invalid attachment. Try again.

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to copy and replace data according to variable

    Choose new color before press the UPDATE BUTTON
    If color is only update then...perhaps...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  6. #6
    Registered User
    Join Date
    01-02-2022
    Location
    Portugal
    MS-Off Ver
    Office 2019
    Posts
    8

    Re: VBA to copy and replace data according to variable

    Quote Originally Posted by bebo021999 View Post
    Invalid attachment. Try again.
    I'm not allowed to post any links or images.

    The error: Lr = wsDes.Cells(Rows.Count, "A"). End( xlUp ). Row

  7. #7
    Registered User
    Join Date
    01-02-2022
    Location
    Portugal
    MS-Off Ver
    Office 2019
    Posts
    8

    Re: VBA to copy and replace data according to variable

    Quote Originally Posted by sintek View Post
    If color is only update then...perhaps...
    Please Login or Register  to view this content.
    This is exactly what I intend. It works. I'll try to adapt it to my sheet and then I say something.
    Thanks

  8. #8
    Registered User
    Join Date
    01-02-2022
    Location
    Portugal
    MS-Off Ver
    Office 2019
    Posts
    8

    Re: VBA to copy and replace data according to variable

    Hello again
    VBA doesn't work in my sheet. I adapted it to the cells in my sheet, but it gives always an error. To save time and effort, I send a second sample, which is a copy of part of my Workbook, with the exact fields and places to enter the data. I have omitted parts and names from the sheets because of confidentiality issues.
    So I would appreciate it if you could correct the VBA according to the available fields. Would it also be possible that when changing the decision, the old decision could be registered in a field that I created in each worksheet as old decision? I remind you that I only intend to change the decision, in the corresponding sheet. The remaining data can remain the same.

    Thank you again
    Attached Files Attached Files

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to copy and replace data according to variable

    Micro
    VBA doesn't work in my sheet. I adapted it to the cells in my sheet, but it gives always an error. To save time and effort, I send a second sample, which is a copy of part of my Workbook, with the exact fields and places to enter the data
    This is the case 99% of the time...In future...always upload sample files depicting actual file setup...
    So much unnecessary time is wasted on code otherwise...

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    01-02-2022
    Location
    Portugal
    MS-Off Ver
    Office 2019
    Posts
    8

    Re: VBA to copy and replace data according to variable

    Quote Originally Posted by sintek View Post
    Micro


    This is the case 99% of the time...In future...always upload sample files depicting actual file setup...
    So much unnecessary time is wasted on code otherwise...

    Please Login or Register  to view this content.

    Yes you are right. I tried to simplify, but I ended up complicating it. As a punishment, I'm going to learn how to build VBA. The solution works.
    Thank you for your help.

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to copy and replace data according to variable

    Glad I could assist...

    Also...As you are new here, you might not know that you can thank those who have helped by adding to their rep...Left of post...
    [* Add Reputation]
    Also...No need to quote entire posts...It just clutters the thread...
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Happy Coding...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy data from an open workbook with a variable name
    By rosshkerr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-15-2021, 12:01 AM
  2. [SOLVED] copy data in a variable range
    By xi603 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2017, 05:19 PM
  3. Copy data to different, variable ranges.
    By Penfoldthethird in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2016, 11:49 AM
  4. copy/paste data and replace - by criteria (column B,C)
    By grzecheem in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-21-2015, 06:39 PM
  5. Copy variable amount of data to new tab
    By melnemac32 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2013, 01:05 PM
  6. [SOLVED] copy and replace data using looping macro
    By cmb80 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2012, 05:17 AM
  7. Drag copy Variable Data
    By Karroog in forum Excel General
    Replies: 7
    Last Post: 12-12-2008, 11:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1