Path to sheet

[Budinski]

Someone was nice enough to write some code that works well by looking at a list of file paths and copying data from those books to a new workbook. Now I would like to copy data from the same list but from another sheet. Is there a way to specify a sheet name/number in a path so that when it runs it will look at the next sheet instead of just the first sheet in each book?

Currently I have a sheet with:

c:\example\book1.xls
c:\example\book2.xls
c:\example\book3.xls etc

This always takes data from sheet1

Can I specify:
c:\example\book1.xls[sheet2]
c:\example\book2.xls[sheet2]
c:\example\book3.xls[sheet2]

I know there are far better ways of doing this but it will save me asking someone to write a whole new macro.

 

[xenou]

Those look like file paths listed on a spreadsheet. More likely its the code itself that needs to be changed, not the list of filepaths...

Somewhere in your code it probably says:
workbooks.filename.worksheets(1)...etc etc.

can be changed to:
workbooks.filename.worksheets(2)....etc. etc.

 

[Budinski]

Thanks for your reply,

I have been trying for days to work out where to make that change. Unfortunately I am usless when it comes to coding. I cant seem to work it out.

Here is the code Im using:

Sub Report()
a = 1:
st:
If Sheets(5).Cells(a, 1) = "" Then GoTo endd
Path = Sheets(5).Cells(a, 1).Text
If Dir(Path) = "" Then
w = MsgBox(Path + " Is Not A Valid Path / File", , "REPORT")
a = a + 1: GoTo st
End If
Application.ScreenUpdating = False
Workbooks.Open Path
Ro = 4:
st1:
If Cells(Ro, 1) = "" Then GoTo NextBook
 CopyIt Ro
Ro = Ro + 1: GoTo st1
NextBook:
ActiveWorkbook.Close savechanges:=False
a = a + 1: GoTo st
endd:
Application.ScreenUpdating = True
Sheets(1).Activate
End Sub

 

[Budinski]

Whoops,

left out the CopyIt section

Sub CopyIt(Ro)
With ThisWorkbook.Sheets(2)
roo = 4
st:
If .Cells(roo, 1) <> "" Then roo = roo + 1: GoTo st
For x = 1 To 17
.Cells(roo, x) = Cells(Ro, x).Text
Next x
End With
End Sub

 

[xenou]

Maybe:

Sub Report()
[COLOR="green"]'-------------------------------------------------------------------------[/COLOR]
[COLOR="green"]'Copy from Sheet 1 or 2 in workbooks...Edited so sheets 1 or 2 can be used[/COLOR]
[COLOR="blue"]SheetToCopyFrom = InputBox("Enter Sheet # to copy from in workbooks:")[/COLOR]
[COLOR="blue"]If Not IsNumeric(SheetToCopyFrom) Then Exit Sub[/COLOR] [COLOR="green"]'//In case user cancels[/COLOR]
[COLOR="blue"]If SheetToCopyFrom = 0 Then Exit Sub[/COLOR] [COLOR="green"]'//In case user enters 0[/COLOR]
[COLOR="Green"]'-------------------------------------------------------------------------[/COLOR]
a = 1:
st:
If Sheets(5).Cells(a, 1) = "" Then GoTo endd
Path = Sheets(5).Cells(a, 1).Text
If Dir(Path) = "" Then
w = MsgBox(Path + " Is Not A Valid Path / File", , "REPORT")
a = a + 1: GoTo st
End If
Application.ScreenUpdating = False
Workbooks.Open Path
[COLOR="Blue"]SheetToCopyFrom.Activate[/COLOR]
Ro = 4:
st1:
If Cells(Ro, 1) = "" Then GoTo NextBook
 CopyIt Ro
Ro = Ro + 1: GoTo st1
NextBook:
ActiveWorkbook.Close savechanges:=False
a = a + 1: GoTo st
endd:
Application.ScreenUpdating = True
Sheets(1).Activate
End Sub

 

[Budinski]

That doesnt seem to work either.

The data I am copying from the second sheet needs to go to the second sheet in the destination book also. I have a bad feeling that I will have to start from scratch with a much more compicated module.

 

[xenou]

Well, you may be able to step through the code and see what's happening "live" so to speak.

See "Step through your code":
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

The debugging step through also gives you the chance to see what's going on - can be instructive (even - don't use full screen for the vbe window and the Exel window so you can actually watch the code working on the Excel side as you step through on the vbe side). The code you have is a little cryptic (st, RO, roo - not very helpful variable names etc.). Perhaps it can be improved. If your stumped, post your requirements with detailed information (Cell addresses, sheet names, locations of key data, etc.).

Alex.

 

[Budinski]

Thanks,

I have reposted here
http://www.mrexcel.com/forum/showthread.php?t=376216

 

[xenou]

Edit: Moved to thread above...