Please suggest a solution to this problem.

[DaveUK]

I have this code in a Workbook called "MASTER" (which opens multiple workbooks from a given directory).

Sub AllWBooks()
Dim sName As String, wb As Workbook
ChDir "C:\Data"
sName = Dir("C:\Data\*.xls")
Do While sName <> ""
Workbooks.Open Filename:=sName
Set wb = ActiveWorkbook
sName = Dir()
Loop
End Sub

The code then opens each workbook in the Data directory in turn.

What i need to do, but am unsure of how to, is (in every opened workbook):

a) Delete rows 1,2 & 3

b) Delete columns B - BA

c) Call a subroutine named "UnmergeAllCells" to do a Cells.Unmerge on all cells in the worksheets.

d) Call a subroutine named "DeleteEmptyRows" to delete empty rows in column A.

e) Call a subroutine named "TruncateLeftString" to restrict column A to 24 characters.

Then i want to copy all cells in column A (with data in them) to a worksheet "Sheet1" iin the "MASTER" workbook. Each workbooks A column data needs to be added to the end of the last copied cells in the A column.

Many thanks for any help given.

 

[scifibum]

a) Delete rows 1,2 & 3

Range("1:3").Delete

b) Delete columns B - BA

Range("B:BA").Delete

c) Call a subroutine named "UnmergeAllCells" to do a Cells.Unmerge on all cells in the worksheets.

It sounds like you already have this subroutine. If so, then just type the name of it on one line to call it. For instance, if the name of the sub was "UnMergeAll" the line in the macro would be:

UnMergeAll

well, this only works if the sub doesn't require any arguments. If it does require arguments you specify them in order, for example if it takes two arguments, the first being a workbook name and the second being a sheet name, it'd be something like this:

UnMergeAll "test.xls", "Sheet1"

On the other hand, if you want to code this directly, it's simple. To do it to the active sheet would be:

ActiveSheet.Cells.Unmerge

To do it to every sheet in the activeworkbook would be:

Dim ws as Worksheet
For each ws in ActiveWorkbook.Worksheets
	ws.Cells.Unmerge
Next ws

d) Call a subroutine named "DeleteEmptyRows" to delete empty rows in column A.

Not sure if you want to delete empty cells in column A or delete entire rows where column A is blank. Again, if you have a sub just enter the name along with any required arguments.

If you need to code this yourself then try a search for "Delete blank rows" or "Delete blank cells" on this forum. It's a popular topic...

e) Call a subroutine named "TruncateLeftString" to restrict column A to 24 characters.

Sounding like a broken record, but you can just type the name of an existing sub within your project or...use the code snippets provided on your other thread which will do this.


OK...


To do various things to every workbook you open with your existing code, you could enter all the code for each action, inserting it after the "Workbooks.Open" line, but before the Loop line of your existing macro.

...hope that helps. I haven't coded out all the specifics but maybe this will help you understand how to accomplish those things.