Posts by Bertie

Thanks Gollem

Will give that a go....however could you or anyone tell me why I'm getting a 'runtime error 1004' for the below? IT has a problem with 'Rows("1").Select' , I've also tried Rows("1:1").Select, but no joy. Apologies but I only have a basic understandering.

Thanks
Bertie

Private Sub CommandButton1_Click()
Range("A1:N37").Select
Selection.PrintOut Copies:=1, Collate:=True
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Delivery Data").Select
Rows("1").Select
Selection.ClearContents
Rows("2").Select
Selection.Copy
Rows("1").Select
ActiveSheet.Paste
Rows("2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Delivery Note").Select
End Sub

Hi

I looking for some help piecing together some macro as well as some extra code that I’m missing.

I currently have a worksheet (A) that is gathering it’s information from another worksheet (B) from the same workbook.

I need (A) to print then to go to (B), clear the top row, copy row 2 and paste into row 1 – (which will update (A)),delete row 2 to move the list up, then print the new results in (A). This needs to continue until it finds an empty row in (B) then it should stop.

Does that make sense?!?

I can write a macro for the copy/paste/delete and print, but do not know how to repeat the macro until the blank row is found in (B).

Thanks for reading

Bertie

Thanks Roy. Forgot the linked cell..D'oh!

Thanks RoyUK

I understand most but how does the TRUE / FALSE bit work? What makes it change from TRUE to FALSE?

Thanks for your help

B

Hi

I'm trying to use a toggle button to hide/show columns D:G but have no idea how to do this code. Can anybody help??

Thanks for your time.

B

Sorry..didn't complete my question correctly should read

Hi

Trying to hold a button on one sheet - when pressed it select another sheet in the same book, selects a COLUMN, then deletes all rows in the selected column that hold a blank cell. I've cobbled together the following but it doesn't work. Any ideas?? Thanks for reading..

Sub Button1_Click()
'
' Button1_Click Macro
' Macro recorded 17/12/2003 by B T
'

'
Sheets("Order Entry").Select
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Range("A1").Select

End Sub

Hi

Trying to hold a button on one sheet - when pressed it select another sheet in the same book, selects a row, then deletes all rows in the selected column that hold a blank cell. I've cobbled together the following but it doesn't work. Any ideas?? Thanks for reading..

Sub Button1_Click()
'
' Button1_Click Macro
' Macro recorded 17/12/2003 by B T
'

'
Sheets("Order Entry").Select
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Range("A1").Select

End Sub

Sorry Will

Just realised that if you change all the array's to 'match' ie instead of A10, B10, C10 to A12, B12, C12 it works fine. I just changed one array and got the error message.

Thanks again for your time..

Thanks Will

This does work, however one problem still remains. The 'data' worksheet is constantly growing - from part 2 of my earlier problem.
When I used your formula and changed the ranges to incorporate blank cells (which will be used at a later date) it returns a #N/A error. Sorry for not outlining this earlier. Is there a workaround for the solution you sent me to deal with this?

Thanks
B

Will, not sure how clear I can make this but here goes…

Have a database

Heading 1 Heading 2 Heading 3
A £10 1
A £20 2
B £5 1
A £25 1
B £5 2
A £10 2
C £10 2
A £5 4
A £25 2

The user needs to tap in their requirement for ‘Heading 3’ on another spreadsheet. So in this example lets say they select ‘2’. Then ALL Heading 1’s need to be grouped and summed by they value under ‘Heading 2’.

So if the user selected 2 then the results, on another Spreadsheet, would be:

A = £55
B = £5
C = £10

Thanks again
B

Can't even get the post to look correct!!! The Headings are: Heading 1 (A letter eg A) Heading 2 (A value eg £20) Heading 3 (A number eg 1)

Thanks for your time as well Will..

Cheers Davey, got it working a treat now. Just need a DSUM expert to happen upon this thread now!!

Thanks again
B

As a beginner have bitten off more than I can chew. Any help gratefully received!!
1. Trying to find a way to use DSUM and select by two criteria from within the equation, maybe nesting in another?? The two criteria are varibable cells from differnt work sheets. Worked fine with just one criteria.

2. I'm trying to set up a macro that runs off a button that will take a set number of rows from of worksheet and then paste it into another. However, I need the pasted(?) data to remain everytime the button is pressed and maybe to look down the first column, find the next blank cell and paste from there. I don't have the knowledge to write it but just record the mouse actions. So for have come up with something like:
Range("A1:A5").Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Sheet2").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub
This works but just copyies and pastes offer previos data which I don't want it to do. It has been suggested that I use 'xldown' and 'Offset' but don't know how to incorporate them into the above.

Thanks in advance.
B