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!!!
Bookmarks