+ Reply to Thread
Results 1 to 1 of 1

Copy/pasting values based on a changing day (multiple workbooks)

  1. #1
    Registered User
    Join Date
    03-07-2011
    Location
    Madison, wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    18

    Copy/pasting values based on a changing day (multiple workbooks)

    I'm clearly no VBA genius and really just know basics so far. The code below works for one day of the month, but I need it to be able to copy up to 30 days (March). I would like it to be monthly, but I can live with some manual changes each month of the code.

    The first range (a88:gr88) is static, but changes files by day (March_1.xlsx, March_2.xlsx, March_3.xlsx, etc...)

    The second range (B792:GS792) changes by day as well. It drops a row, so for example:

    March 1 data goes here: B792:GS792
    March 2 data goes here: B793:GS793
    March 3 data goes here: B794:GS794
    etc...

    I'd also like to close strFirstFile after copy/pasting, but can't figure out how to do that, so I decided to just close the application because I don't want the data saved on file one anyway.

    Sub TEST()

    Dim wbk As Workbook
    Dim strFirstFile As String
    Dim strSecondFile As String

    strFirstFile = "\\testfolder\March_1.xlsx"
    strSecondFile = "\\summary\AnnualFile.xls"

    Set wbk = Workbooks.Open(strFirstFile)
    With wbk.Sheets("Data")
    .Range("A88:GR88").Copy
    End With
    Set wbk = Workbooks.Open(strSecondFile)
    With wbk.Sheets("Daily Totals")
    .Range("B792:GS792").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    wbk.Save
    wbk.Close
    Application.Quit
    Application.DisplayAlerts = False

    End With

    End Sub



    Any help is appreciated!
    Last edited by evilgrinners; 03-16-2012 at 10:12 AM. Reason: turned on email notifications

+ 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