VB Copy, Paste and dynamic print range

Todd R K

Board Regular
Joined
Nov 3, 2004
Messages
90
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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:

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

Board Regular
Joined
Nov 3, 2004
Messages
90
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

Board Regular
Joined
Nov 3, 2004
Messages
90
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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

Board Regular
Joined
Nov 3, 2004
Messages
90
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
 

Forum statistics

Threads
1,136,520
Messages
5,676,340
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top