Selecting Specific range of data using Macro

[Closed]
Report
Posts
1
Registration date
Thursday July 23, 2015
Status
Member
Last seen
July 23, 2015
-
Hello,
I have made 20 sheets for a single Excel File. I need to select only specific data and display(for ex; I have two Macro buttons called 'DEBUT' and 'FIN' in around 14 worksheets. Now, I need to display all the columns which is between 'DEBUT' and 'FIN'.)
When I tried the below program I am just getting al the data and Images which are available in all the 20 sheets but my requirement is to select only particular range of data and show it as an output. Can anyone help me out?? Please.

Sub copy_sheet()
Dim WS As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long, lCol As Long, lRow2 As Long
Dim rng As Range
Dim offsetVal As Long

Application.ScreenUpdating = False
Sheets("SYNTHESE").Cells.ClearFormats
Sheets("SYNTHESE").Cells.ClearContents
Sheets("SYNTHESE").Activate
offsetVal = 1
For Each WS In Worksheets
If WS.Name <> "SYNTHESE" Then
With WS
lRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
Debug.Print lRow
lCol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
Debug.Print lCol
Set rng = .Range(.Cells(1, 1), .Cells(lRow, lCol))
With rng
.copy
End With
End With
Worksheets("SYNTHESE").Cells(Rows.Count, 1).End(xlUp).Offset
(offsetVal, 0).PasteSpecial (xlPasteAll)
'offsetVal = offsetVal + lRow + 2
End If
Next WS

End Sub

Sub CopyImages_Controls()
Dim sh As Shape
Dim WS As Worksheet
Dim selRange As Range
For Each WS In Worksheets
If WS.Name <> "SYNTHESE" Then
For Each sh In WS.Shapes
sh.copy
Sheets("SYNTHESE").Cells(sh.TopLeftCell.Row,
sh.TopLeftCell.Column).Activate
Worksheets("SYNTHESE").PasteSpecial
MsgBox sh.Name & "---" & sh.TopLeftCell.Row
Next sh
End If
Next WS
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!