+ Reply to Thread
Results 1 to 4 of 4

Macro to copy over cell value rather than actual cell (code included)

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-01-2012
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    121

    Macro to copy over cell value rather than actual cell (code included)

    Private Sub DailyCountExport()
    
    Dim wb As Workbook
    Dim ws As Worksheet
        
    
        Set wb = Workbooks.Open(fileName:= _
            "C:\Daily Count.xls")
        Set ws = wb.Worksheets(1)
        With ws
        
            nextrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        End With
        
            With Workbooks(2).ActiveSheet
            
            
            ws.Cells(nextrow, "A") = Format(Date, "dd-mmm")
            Workbooks(2).Worksheets("Daily Statistics").Range("B21").Copy ws.Cells(nextrow, "B")
          
            
    
    
    
          
        End With
        
        wb.Save
        wb.Close
        
        Set ws = Nothing
        Set wb = Nothing
    
    
    
    End Sub
    This copies over a daily count and exports it into a file so that the user does not have to do it daily, as is, it works fine, but I might need to re-tweak it due to drive availability issues. What I would like to do is instead of copying B21, copy the value of B21, because right now it is pulling the formatting as well, when I just want the value. Any ideas?
    Last edited by jayinthe813; 10-01-2012 at 09:38 AM.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to copy over cell value rather than actual cell (code included)

            ws.Cells(nextrow, "A") = Format(Date, "dd-mmm")
           ' Workbooks(2).Worksheets("Daily Statistics").Range("B21").Copy ws.Cells(nextrow, "B")
            ws.Cells(nextrow, "B").value = Workbooks(2).Worksheets("Daily Statistics").Range("B21").value
        End With
    If solved remember to mark Thread as solved

  3. #3
    Forum Contributor
    Join Date
    08-01-2012
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: Macro to copy over cell value rather than actual cell (code included)

    thanks, I made the mistake in not using .value for both sides, kept erroring. +rep

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to copy over cell value rather than actual cell (code included)

    no mistake, they are 2 different ways to make a copy

    range(source).copy range(dest)
    or
    range(dest).value = range(source).value

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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