VB Copy, Paste and dynamic print range

[Todd R K]

Good afternoon:

Background

Each month, new data is populated in column C on the worksheet titled "Data". The data from column C is copied to the "Report" worksheet. The "Report" worksheet contains historical monthly data but only the most recent 13 months are printed (rolling 13 month report)

I am attempting to pull together some code that will perform the following:

1. Copy column C from "Data" worksheet and paste the column to the next available column on the "Report" worksheet.

2. Adjust the print range to include the new column and the 12 previous columns (to capture the rolling 13 months).

3. Hide the column that is no longer a part of the rolling 13 months (column E in my example)

I recorded a macro and came up with this code but I do not know how to tell the code to find the next available column in the subsequent months in order to perform the paste or how to change the print range the next time the macro is invoked.

Any help would be appreciated.

Todd


Sub CopyPasteDynamicPrint()
'
' CopyPasteDynamicPrint Macro
' Macro recorded 09/29/2006 by Todd Keckhafer
'

'
Sheets("Data").Select
Columns("C:C").Select
Selection.Copy
Sheets("Report").Select
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=-8
Range("A1:C130").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.SmallScroll Down:=-132
Range("F3:R130").Select
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$F$3:$R$130"
End Sub

 

[pgc01]

Hi Todd

I wrote this code based in your specs but since you don't give enough information you may need to adjust 2 things:

First

The code calculates which columns to include in the print area. However in your post you don't say which rows you want to include. I used the ones in your recorded macro, from row 3 to row 130.

Second

To find which is te next column available in worksheet 'Report' I need to know one row that will never be empty in the monthly data. Since you don't say anything about that, I assumed that the first row to print, row 3, always has data every month.

Please try this code:

Sub CopyPasteDynamicPrint()
Dim lNACol, lFirstCol

With Worksheets("Report")

    ' Next available column in worksheet Report
    lNACol = IIf(.Cells(3, 1) = "", 1, .Cells(3, .Columns.Count).End(xlToLeft).Column + 1)

    'Copy data
    Worksheets("Data").Columns("C").Copy Destination:=.Columns(lNACol)
    Application.CutCopyMode = False

    'First column to show/print
    lFirstCol = IIf(lNACol > 13, lNACol - 12, 1)

    ' Set print area
    .PageSetup.PrintArea = .Range(.Cells(3, lFirstCol), .Cells(130, lNACol)).Address

    ' Show only the last 13 months
    .Columns.Hidden = True
    .Range(.Cells(1, lFirstCol), .Cells(1, .Columns.Count)).EntireColumn.Hidden = False

End With
End Sub

Hope this helps
PGC

 

[Todd R K]

PGC

Thank you! Your assumptions are correct with regard to items 1 and 2. I will try the code and let you know how it works out.

Thanks again for your time.

Todd

 

[Todd R K]

PGC

I copied the code into a module and when I tried to invoke the macro, I received a "Compile error:" - Expected sub, function or property.

I don't know enough about VB to know what this means.

Any help would be appreciated.

Thank you

Todd

 

[pgc01]

Todd

I've just tried the code and got no error.

This are the steps I took

- run excel
- rename 2 of the sheets to Data and Report
- insert the code in a general module
- write some info in column C beginning in C3
- run the code several times

Each time I ran the code the data in column C was copied to the ws Report, as expected.

Can you please tell me when you get the error which line of code gets highlighted?

Kind regards
PGC

 

[Todd R K]

PGC

Works perfectly now. The first time, I had copied the code into a blank worksheet and then copied it into the VB module (apparently VB does not like this approach). Today I copied the code directly from the post into the VB module and it worked like a charm.

Thank you!!

Todd

 

[pgc01]

Great! I'm glad it works now.

Cheers
PGC