Copying data range from one workbook to another

[vbanovice123]

Hi,

I have a workbook containing data range that increases by a column every month.
example if the column ends at P, next month column ends at Q and so on. Rows are constant.

How can I copy the data range from the active workbook to an existing workbook template having the same workseet tab names as the source workbook?

Also, the source workbook has a name for the data range using Name Manager.

I have the below code that I can apply by tweaking the but I need to have the macro code increment the column for next month's run.


'Agency
'copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
copy_range "Govt_Rural", "Govt_Rural13M", slidenum, arheight, arwidth, artop, arleft, 1

 

[vbanovice123]

Also, when in the month after a quarter, example in July, the column data that displays April, May and June data in columns P, Q and R will collapse and move back to column P as 3Qtr2011 (3rd Quarter in 2011).

Hence the data range is variable and moves forward and backward depending on the month and quarter.

Is there a macro to copy a data range having a range that is variable in columns but constant in the rows?

 

[vbanovice123]

Can someone please help?

Can I expand the range to two columns, then delete empty cells when copying the range?

For example if the range is having data in columns P and then next monthend range has data till column Q, but if I specify a rang till column P, then the macro will not include column Q in the selection. However if I expand the range to a wider column and then just delete the empty cells, this should take care of the including the additional columns for subsequent months.

Do you know of a better way to get around this?

Thanks

 

[vbanovice123]

Hi

The below code work fine and pastes the sheet into another workbook. But the format appears weird. Font is too big and some column values n? I tried some of these options but nothing worked. Same issue.


Sub CopyTo13MonthHardCopy()
'
' CopyHardCopy Macro
'

'
'HFI
'copy_range_sheet(sheet, rngname)
copy_range_sheet "HFI", "HFI13M"

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton2_Click()

Call CopyTo13MonthHardCopy

End Sub




'Public Function copy_range(sheet, rowStart, columnStart, row_count, columnCount, slide, aheight, awidth, atop, aleft)
'Public Function copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
Public Function copy_range_sheet(sheet, rngname)

Sheets(sheet).Select
'Cells(rowStart, columnStart).Resize(row_count, columnCount).Select
Range(rngname).Select
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Range(rngname).Activate
Selection.Copy
Workbooks.Open Filename:= _
"C:\Users\Myfolder\Downloads\OnePagers13MonthHardCopy.xlsx"
Sheets("HFI").Select
ActiveSheet.Paste

End If


End Function

 

[vbanovice123]

Can someone help tell me why the format and data being pasted are not the same? Should I use any special paste function?

Thanks for your help

 

[vbanovice123]

Actually I got it to work for now by uing Record Macro and doing the teps manually. I figured that the range is pasted as a picture, then the zoom needs to be set to 40% from the original 100%. I also wa able to save the file via the macro code.

Thanks.