Column/row and formatting referencing - every help appreciated!

I cannot think of a way that Excel could do what you want without VBA.

Does all the data on sheet2 require formatting exactly like on sheet1?

Run the VBA in the attached workbook with {CTRL} t

this assumes that last occupied cells in row1 and columnA of sheet2 define the size of the range

Place in a general module:

Please Login or Register  to view this content.

I am a bit confused
- perhaps you could attach your workbook so that I can see both sheets (click Reply, Go Advanced, scroll down to Manage Attachments etc) - cutdown to below 1mb
- I assume this is financial info - convert any text into alphabet soup and the numbers will be meaningless

I think it may be easiest to create a macro that toggles between 2 settings on sheet 1
- normal view
- print view (hidden columns, filtered rows)

Then copy/paste print view to sheet 2

Without seeing what you are trying to achieve it's a bit tricky
If you do not want to post your workbook, then can you explain the differences between the 2 sheets

How are you filtering the data?
- are you manually selecting?
- are you using advanced filter?
- VBA could apply the same filter to the 2nd sheet (if all the relevant fields are present)

Are some columns visible on sheet 1 that are hidden on sheet 2
- can you list the columns that should be hidden

ok - I will see what I can do

I am not suggesting this as the solution necessarily but would this yield the correct values on sheet2?

- Paste everything from sheet 1 to sheet 2 with nothing filtered or hidden
- Hide every row outside the print area
- Hide every column outside the print area
- Do we need to hide anything inside the print area?
- Does the "print area" include all the headings of your columns that you need pasting to other sheet?
(are you using page setup "rows repeated at top" for your printing)

please amend as necessary - I am trying to get the correct picture in my head

thanks
Kev

Give this a try - it seems too simple but it may do what you need
It basically hides (in Sheet2) any columns to the right and left of the print range and shows all columns inside the print range

Replace names for sheets1 & 2

Assumption1
I have assumed that sheet2 contains ALL the values in sheet1 (all columns , all rows) using formula

Formula:

Please Login or Register  to view this content.

(ie everything that appears on sheet1 automatically appears on sheet2 in the exact same cell)
If that assumption is not correct - please create sheet with that assumption satisfied and see if it gives you what you want.

Assumption2
The print range is one contiguous block.
If that is not correct please explain what you do

Please Login or Register  to view this content.

If the above gives you the range correctly, I will add in row height and column width matching

Getting at the height of rows is the tricky bit.
Unfortunately, when hidden via filtering, it appears that the only attribute that VBA can get its hands on returns a height of zero (due to the filtering), which is rather annoying - I have a workaround (hence the question below) but I would prefer a simpler solution

How complex is your filtering and is it regularly varied?
What I am thinking is that VBA [or you manually] could remove the filter to allow all the data to be copied to the other sheet - allowing the row to be set at the correct height
- provided data is not sorted AFTER the filter is removed, row number in sheet2 matches sheet 1 irrespective of filtering

Incidentally, what you are trying to do is quite the opposite of what Excel has available in its standard toolkit
Using Advanced Filter - you can automatically paste filtered values to another sheet leaving the original sheet unfiltered
- unfortunately whilst cell formatting does transfer with the data, column widths and row heights do not

I had become blinkered (and obsessed!) with finding the height of the row whilst the data was filtered - when it was not necessary
Blinkers off:

Amend sheet names for ws1 and ws2

Please Login or Register  to view this content.

No problem -busy myself for next two weeks - so that suits me too!

Good - very glad it's working the way you want.
Kev