Create new Excel sheet from existing

[cmonthetic]

Hi,

I setup some forms in Excel to be used as data entry, all of the forms work fine and the data is entered on to separate workheets as required.

What I need to do is to be able to select parts of the different worksheets and merge/copy the information into an empty worksheet so that it can be printed as if it is all done on 1 sheet.

This would be simple if the worksheets were always going to be a set size, this is not the case and as the number of rows will change depending on the individual users input, the columns are static.

I have thought about trying to find the first empty cell, then selecting everything along to the last known column, then copying across to a fresh worksheet but am unsure how to proceed with this.

Anyone help on this would be appreciated.

TIA
FBICFC

 

[SkipVought]

Hi,

Check out the UsedRange property

With Sheets("Report").UsedRange
  r = .Row + .Rows.Count
End with
Sheets("A").Usedrange.Copy _
  Destination:=Sheets("Report").Cells(r, 1)

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[cmonthetic]

Thanks for the reply SkipVought.

To a certain extent the number of rows is fixed, but 2 of the 11 columns have formulas in them.

Do you know if the UsedRange property will ignore the formulas?

Cheers

FBICFC

 

[PHV]

Take a look at the PasteSpecial method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

It is the Paste Method that will copy formulas. As PHV suggest, the Paste Special - Values method will not copy formulas.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}