Getting data from specific location in a workbook

[vijay.vizzu]

Dear All,


I have an workbook with around 200 sheets (not sure).Actually this was an PO File in specific format. Now i wish to get the data from spcific range(D2:G8) which contains vendor address along with Vendor code. The specified range (D2:G8) fixed in all sheets in a workbook. So can you tell me how to copy the data from all sheets to a seperate sheet.


Thanks in advance.

 

[SirJB7]

Hi, vijay.vizzu!


Add this code to any module of your workbook (Alt-F11, Insert, Module), set the value for constant ksWsMaster to the summary sheet name, and then run the macro.


-----

[pre]

Option Explicit

Sub X()
' constants
Const ksWsMaster = "Hoja1"
Const ksRange = "D2:G8"
' declarations
Dim I As Integer, J    As Integer, K As Integer, L As Integer
Dim rng As Range
' start
With Worksheets(ksWsMaster)
.Activate
.Cells.ClearContents
End With
L = 0
' process
For I = 1 To Worksheets.Count
With Worksheets(I)
If .Name <> ksWsMaster Then
L = L + 1
Cells((L - 1) * 8 + 1, 1).Value = .Name
Cells((L - 1) * 8 + 1, 2).Value = L
Set rng = .Range(ksRange)
For J = 1 To rng.Rows.Count
For K = 1 To rng.Columns.Count
Cells((L - 1) * 8 + J + 1, K + 3).Value = rng.Cells(J, K).Value
Next K
Next J
End If
End With
Next I
' end
Cells(1, 1).Select
Beep
End Sub

[/pre]
-----


Regards!

 

[vijay.vizzu]

Thank You, SirJB7, It works fine.

 

[SirJB7]

Hi, vijay.vizzu!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!