Excel 2010 and macros

[Lynton]

Evening all,

Now I am pretty good with excel and macros., VBA coding etc however I am stumped.

Anyone a genius?

Basically in column A I can have between 2 and 30 names and in column B the full file path to each individual spreadsheet.

I need to write some code to go to the first pathway open it copy data into column B of a master spreadsheet, close it, go to the next, copy data from that to column C of the master spreadsheet and repeat until no more to do.

The hard bits are

1) repeat until no more pathways

2) 1 to B, 2 to C etc etc...


any help greatly appreciated!

 

[Shadow]

It's been years since I've written macro in Excel but for part 2 of your problem could you use the R1C1 cell format and use an incremental count in the C structure?

 

[sep9001]

This might help get started with the loop through the worksheet:

Public Sub SortData_01()

Dim lngRows As Long
Dim lngR As Long
Dim lngCol As Long
Dim lngOldCol As Long
Dim irow As Integer

lngR = 4
lngCol = 7

'Insert new worksheet

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Name = "Amended"



Sheets("Amended").Select


Sheets("Data").Select


'Get the number of rows
irow = 5
Do While Sheets("Data").Cells(irow, 1) <> ""
irow = irow + 1
Loop


For lngRows = 5 To irow - 1

For lngCol = 7 To 27
If Sheets("Data").Cells(lngRows, 1) <> "" Then
lngR = lngR + 1





End If

lngCol = lngCol + 1
Next lngCol
Next lngRows



End Sub

 

[Alastair]

There is a more elegant way that uses a command to count the non-blank rows, from memory it's xlDown but it's been some years since I wrote a macro to do that. I know there's a macro that does something similar in a sheet I'm working on for a client. I can only work on it on their premises for DPA reasons, but I should be able to get an anonymised copy of the macro next week.

 

[Lynton]

cheers guys.