+ Reply to Thread
Results 1 to 5 of 5

VBA to copy value from cells in 1 worksheet to another, but without erasing existing data

Hybrid View

  1. #1
    Registered User
    Join Date
    01-24-2016
    Location
    Corpus Christi, TX
    MS-Off Ver
    2010 Pro
    Posts
    3

    VBA to copy value from cells in 1 worksheet to another, but without erasing existing data

    Hello, I am trying to copy data from 1 worksheet to another but with some special features. The data I am copying are values from formulas that calculate a number. This resides on Worksheet "Night Force". I need the value from F59 and I59. I want these values placed into specific cells based off the date. So I have my 2nd worksheet "Table". The entire worksheet is blank. I have already created a button that will run the code. When I press the button, I want the code to look at the last Column (row 1) for the current date, then update Row2 (same column) with Night Force.F59 and Row3 (Same column) with Night Force.I59. If the Last Column does not contain the current date, then I would like the code to create a new column, with today's date and update the corresponding cells (Row2, Row3) underneath said column. So if I were to run the button today (1/24/16) then A1 would populate with the date (1/24/16) and A2 would populate with the value of F59 from Night Force worksheet (162 for example) and A3 would populate with value from I59 from Night Force worksheet (123 for example). For everytime I press the button on 1/24/16 those 2 cells would update with the correct values. When the day changes and I press the button, then B1 would get the new date (1/25/16) and B2 and B3 would update respectively with the values from I59 and F59. Every time I press the button during the day of 1/25 then only B2 and B3 would update accordingly. Next day (1/26) C1 would have the new date and C2 and C3 would update.

    I have very minimal written code for it, but the problem is everything is statically defined which defeats the purpose of having the macro. I could just as easily update the cells myself with the same amount of typing it takes to change the code.

    Thank you in advance for your help.

    Skellington

  2. #2
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: VBA to copy value from cells in 1 worksheet to another, but without erasing existing d

    Hi there!
    I've managed to do it this way:
    Option Explicit
    
    Sub jskellington2021()
        
        Dim lefty As Long
        Dim NF As Worksheet
        
        Set NF = Sheets("Night Force")
        With Sheets("Table")
            lefty = .Cells(1, .Columns.Count).End(xlToLeft).Column
            If lefty = 1 And .Cells(1, lefty) = "" Then
                .Range("A1") = Date
                .Range("A2") = NF.Range("F59")
                .Range("A3") = NF.Range("I59")
            Else
                If .Cells(1, lefty) <> Date Then lefty = lefty + 1
                
                .Cells(1, lefty) = Date
                .Cells(2, lefty) = NF.Range("F59")
                .Cells(3, lefty) = NF.Range("I59")
                
            End If
        End With
    End Sub
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  3. #3
    Registered User
    Join Date
    01-24-2016
    Location
    Corpus Christi, TX
    MS-Off Ver
    2010 Pro
    Posts
    3

    Re: VBA to copy value from cells in 1 worksheet to another, but without erasing existing d

    Sub Table_Update()
      Dim tDate As Date
      Dim dAct As Integer
      Dim nAct As Integer
      Dim LC As Long
      tDate = Date
      dAct = Sheets("Night Force").Cells(59, "F").Value
      nAct = Sheets("Night Force").Cells(59, "I").Value
      LC = Sheets("Table").Cells(1, Columns.Count).End(xlToLeft).Column
    
      If Cells(1, LC) = tDate Then
        Cells(2, LC).Value = dAct
        Cells(3, LC).Value = nAct
      Else
            Cells(1, LC + 1) = tDate
            Cells(2, LC).Value = dAct
            Cells(3, LC).Value = nAct
      End If
    End Sub

  4. #4
    Registered User
    Join Date
    01-24-2016
    Location
    Corpus Christi, TX
    MS-Off Ver
    2010 Pro
    Posts
    3

    Re: VBA to copy value from cells in 1 worksheet to another, but without erasing existing d

    Both codes work, but Bulina2k's is cleaner/shorter

  5. #5
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: VBA to copy value from cells in 1 worksheet to another, but without erasing existing d

    Thanks for the rep.
    Be carefull cause your code doesn't take in consideration the situation when the Table worksheet is completely empty (first use).
    Have fun!

+ 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. [SOLVED] Macro to copy rows from the data sheet to existing worksheet
    By terence8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2014, 08:08 AM
  2. Replies: 11
    Last Post: 11-04-2013, 04:32 PM
  3. Replies: 3
    Last Post: 10-14-2013, 03:06 PM
  4. Macro to copy row into existing worksheet when data entered
    By Backroads23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2012, 02:31 AM
  5. [SOLVED] Copy data from several worksheets to one existing worksheet
    By e.nekram in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-22-2012, 07:17 AM
  6. Replies: 0
    Last Post: 04-28-2012, 06:06 PM
  7. Need to copy specific cells into an existing worksheet based on date entered by user
    By jrfleury in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2011, 09:44 AM

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