Please suggest a solution to this problem.

DaveUK

Board Regular
Joined
Jan 24, 2005
Messages
245
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
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:
Code:
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.
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top