Results 1 to 1 of 1

VBA to Copy Cells/Formulas From One Workbook, Into Current Workbook With Multiple Sheets

Threaded View

Rick K VBA to Copy Cells/Formulas... 07-01-2015, 03:33 PM
  1. #1
    Registered User
    Join Date
    08-05-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    70

    VBA to Copy Cells/Formulas From One Workbook, Into Current Workbook With Multiple Sheets

    Hi All,
    As a beginner I like to research and try and do the work before coming up here, but I think I've about had it! Been working on this one for a few weeks, and I "think" I'm there, but now I'm stumped. A little background is below, but I'm wondering if someone could look at my code and tell me what it is that I've completely screwed up... Also I've attached 2 small example files.

    Background:
    I get an AR Aging workbook, containing 6 worksheets each week (attached file only has 3, to keep the size down). The data in each worksheet varies, but the layout is exactly the same, just a different number of rows. I also have what I call an Aging Header File, that I copy over into each worksheet, which then looks at the dollar amount in each row, and populates that number in the correct column, based upon the number of days it's outstanding.

    I am looking to automate this process, so that my code pulls the Aging Header Cells into each worksheet, and copies it down, then moves to the next worksheet and so on. I don't need it to total up the columns, yet, that comes next.

    My code is below. I open the "Aging Header" file, and select the cells, then switch over to the workbook containing my data. It needs to paste into Cell N1 on each of the worksheets. Then it needs to copy down, then move to the next sheet. Right now, it's hanging up and not switching to the file I want to paste it into.

    SO, the bottom line is: Open the aging file. Open the aging header file. For each worksheet in the aging file, paste the aging header cells in Col N1, then copy/paste all the way down, move to the next worksheet, done.

    Sub AgingHeaderCopy()
    Dim ws As Worksheet
    ' Application.ScreenUpdating = True


    Workbooks.Open Filename:="C:\FILES\AGING HEADERS.XLSX"
    'This file contains Aging Buckets (current, 30, 60 90 days, etc.), with forumlas in the next row.
    'These formulas read the "Days" column in the main workbook, and populate the aging buckets with the invoice amount
    'based on where the "days" would fall in the aging buckets.

    'EXAMPLE: IF DAYS WERE 41, THE DOLLAR AMOUNT WOULD POPULATE IN THE 31-60 DAY AGING BUCKET.
    'IF IT WERE -16, IT WOULD POPULATE IN THE "CURRENT" AGING BUCKET, AS IT'S NOT DUE YET.

    'Range A1:N2 are the cells from this file that need to be copy/pasted into each sheet, beginning at N1

    Range("A1:I2").Select
    Selection.Copy

    'Switch Over To Aging Files workbook - NOT SURE IF THIS IS THE CORRECT SYNTAX FOR SELECTING THE AGING FILE.

    For Each ws In ActiveWorkbook.Worksheets

    ws.Activate
    With ActiveSheet

    'Copy cells in from Aging Header.xlsx (A1:N2), and put them in N1
    Range("N1").Select

    'Paste cells from Aging Headers.xlsx in each sheet, beginning at N1 (FORMULAS TO COPY WILL BE IN RANGE N2:V2)
    ActiveSheet.Paste

    'Now copy the formulas from N2:V2, all the way down to the end (Lrow)
    Range("N2:V2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("N2:V33670")
    'Selection.AutoFill Destination:=Range("N2:V50000")
    'Move to the next worksheet, and repeat the process

    'The above is not really correct, it came from a macro i recorded, and, there is always a different amount of rows in each tab,
    'week over week. Need an Lrow statement here somehow

    End With

    Next ws

    End Sub


    THANK YOU!!!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy sheets from current workbook to another workbook
    By RoMarius1981 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2014, 09:06 AM
  2. [SOLVED] Copy range to new workbook w/some cells as values & some cells as formulas in new workbook
    By happydayze in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2013, 12:42 PM
  3. [SOLVED] Macro to copy cells from multiple sheets in workbook to multiple sheets in other workbook
    By KeithMale in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-13-2013, 05:37 PM
  4. [SOLVED] Conditionally Copy Cells from Multiple Sheets and Paste in Another Workbook
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2012, 09:22 AM
  5. Copy Cells from New Workbook to Current Workbook
    By gav0101 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-02-2010, 05:04 PM

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