posted January 24, 2005 02:24 PM         

---

I looked at your syntax, and unable to find anything wrong with it, due to really not knowing that stuff to well. I would go here and see if they can help you http://www.mrexcel.com/board2/

------------------
Powered by Intelligent Computing Solutions.
------------------------
www.footslog.com

www.compsol.8k.com

For every problem, there is a solution.

IP: Logged

posted January 20, 2005 04:08 AM         

---

There is a compile error when I run it:
For without Next, and the debugger highlights the End Sub.

The code did work when I had it in the tab so as soon as you selected the worksheet it merged the 2 reference worksheets, but it is not working as well now as a button on the sheet.

------------------

IP: Logged

posted January 19, 2005 10:34 PM         

---

Does anything happen when you run the macro, or does it not work right?

------------------
Powered by Intelligent Computing Solutions.
------------------------
www.footslog.com

www.compsol.8k.com

For every problem, there is a solution.

IP: Logged

posted January 18, 2005 10:20 PM         

---

We are looking into this for you.

------------------
Powered by Intelligent Computing Solutions.
------------------------
www.footslog.com

www.compsol.8k.com

For every problem, there is a solution.

IP: Logged

posted January 18, 2005 07:11 AM         

---

Hi - can anyone help?

I have 2 Excel worksheets of information that I need to merge. They are in identical format – and I have managed to put together (with a little help) a macro to roughly merge these (see below), although Im having trouble getting this to work as a button.

The combined data then needs to be sortable by various criteria (I’m currently using the autofilter)

Then finally I need to produce 2 versions of the combinded and sorted data, one with more detail that the other, in a specified format, without having several, or many blank rows present!

Im trying to put this together using macros with buttons to allow other people to use it, but am currently struggling myself! Any help would be much appreciated!

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim r As Long

'Turn screen updates off
Application.ScreenUpdating = False

'Delete everything in Master sheet, from row 5 downwards
ActiveSheet.UsedRange.Offset(5).Clear

'Loop through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Do this for all sheets except the Master sheet
If ws.Name = "Int" Then
'Copy from 5th row and downwards
ws.UsedRange.Offset(5, 0).Copy
'Paste values to Master sheet, below last used row
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).PasteSpecial xlPasteValues
End If
Next ws

'Loop through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Do this for all sheets except the Master sheet
If ws.Name = "NA" Then
'Copy from 5th row and downwards
ws.UsedRange.Offset(5, 0).Copy
'Paste values to Master sheet, below last used row
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).PasteSpecial xlPasteValues
End If

'Remove completely empty rows
For r = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r

'Select cell A1
Range("A1").Select

'Turn after-copy-blinking off
Application.CutCopyMode = False

'Turn screen updates back on
Application.ScreenUpdating = True
End Sub

IP: Logged

All times are CT (US)