Pasting a Dynamic Range of cells to a new workbook

[imported_unknown]

How do you do this with code. I would like to automate the copying and pasting of cells to a new worksheet (monthly). For some reason, our users keep messing up the results???? I am at a loss of ideas?? Any suggestions would be great.

Thank you in advance for your support.

Mahalo!

 

[sagain2k]

Try the formula: =Sheet1!A1 into a cell in Sheet3 (for example). Then you can Copy/Paste or use Fill Right and Fill Down from that formula to automatically generate more refereces.

If you want VBA to create that reference formula for you, something like the code below will work. Hope this helps!

--Ray

'ENTERING THE REFERENCE FORMULA
ActiveCell.Formula = "=Sheet1!A3"

ActiveCell.Copy 'COPIES THE REFERENCE

'SELECTS THE RANGE FROM ACTIVE CELL TO
'CELL OFFSET BY 4 ROWS & 6 COLUMNS
Range(ActiveCell.Address & ":" & ActiveCell.Offset(4, 6).Address).Select

ActiveSheet.Paste 'PASTES ALL THE REFERENCES