copy paste special value

[ysherriff]

Can someone please tell me why my copy paste special value is not working in this macro? I have attached the worksheet and the macro name is consolidatesheet.

Thank you for your help.

---------------------------------------

Sub ConsolidateSheets()
Dim TargetSh As Worksheet
Dim DestCell As Range
Dim LastRow As Long
Dim sh As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

' Delete the sheet "SUMMARY" if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("SUMMARY").Delete
On Error GoTo 0
Application.DisplayAlerts = True

On Error Resume Next
Set TargetSh = Worksheets("SUMMARY")
On Error GoTo 0
If TargetSh Is Nothing Then
Set TargetSh = Worksheets.Add(before:=Sheets(1))
TargetSh.Name = "SUMMARY"
Else
TargetSh.Cells.Clear
End If

Set DestCell = TargetSh.Range("A1")
Sheet7.Range("TEMPLATE_HEADER").Copy DestCell 'copy header from template
Set DestCell = DestCell.Offset(1, 0)

' copy individual dsm sheets to summary sheet
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "CONTROL-HOSPITAL" And sh.Name <> "HOSPITAL MASTER LIST" And sh.Name <> "UNLISTED HOSPITALS" And sh.Name <> "PSR-GLC LIST" And sh.Name <> "PSR-GLC LIST" And sh.Name <> "SUMMARY" And sh.Name <> "INSTRUCTIONS" And sh.Name <> "TEMPLATE" And sh.Name <> "CONTROL-LIVINGCENTER" Then
LastRow = sh.Range("D50000").End(xlUp).Row
If LastRow > 1 Then
sh.Range(Range("SUMMARY_START_CELL").Address & ":" & sh.Range("F" & LastRow).Address).Copy TargetSh.Range(DestCell.Address)
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DestCell = DestCell.Offset(LastRow - 8)
End If
End If
Next

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
ActiveSheet.UsedRange.EntireColumn.AutoFit 'AutoFit the column width
Sheets("SUMMARY").Visible = False 'hide sheet
End Sub

 

[NARAYANK991]

Hi ,

It will work with this statement :

              sh.Range(Range("START_CELL").Address & ":" & sh.Range("F" & LastRow).Address).Copy
              TargetSh.Range(DestCell.Address).Select
              Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                      :=False, Transpose:=False

Narayan

 

[ysherriff]

Thanks Narayan. It worked but can you tell me why i have extra spaces between each last row.

The file is rather large so i have provided link thru dropbox.

https://www.dropbox.com/s/ujtxippko2uqkd4/Market Hospital Alignment Template v.07.xls

 

[NARAYANK991]

Hi ,

Change the following statement :

LastRow = sh.Range("D50000").End(xlUp).Row

to

LastRow = sh.Range("E50000").End(xlUp).Row

Narayan

 

[ysherriff]

Thank you Narayan for all your help. I appreciate it. Let me see if it works....

 

[ysherriff]

One last thing. On the drop down validation list, how can i make it go to the first data in the list?

I have the following sub function:

Sub DropDown1047_Change()
Range("Selected_City") = 1
End Sub

But it only works on form controls and not data validation.

Regards

 

[NARAYANK991]

Hi ,

If you are working with Data Validation , you are working with worksheet cells , so instead of using the Form Control event procedure , you can use the Worksheet_SelectionChange event procedure , where you can check if the cursor is moved to the cell which contains the DV drop-down , the cell will immediately display the first item in the list , irrespective of what might have been selected earlier.

Of course , if you also have a Worksheet_Change event procedure , then you should have statements :

Application.EnableEvents = False

and

Application.EnableEvents = True

around the code where you modify the cell which has the DV drop-down.

Narayan

 

[ysherriff]

Excuse me for my ignorance Naraya but how do i do a Worksheet_Change event procedure? Can you direct me to a link that explains for my education and how to apply it in this instance?

Thank you again.

 

[NARAYANK991]

Hi ,

I have no doubt that you will find a lot of information on the Internet if you just Google for this ; I'll give you a brief explanation.

Your present event procedure is triggered by a change to the DropDown1047 control ; when ever you change the selection in that control , it ensures that the Range named Selected_City is put to 1 , which I assume will then display the first item in a list of cities.

Now instead of a control , if you have a Data Validation drop-down , it is in a worksheet cell ; suppose this is in a sheet tab named X ; you will have to introduce the Worksheet_Change event procedure in the sheet X section.

See the attached file for an example. If yours is different from what I have done , let me know how it differs so that I can revise my file and re-upload.

Narayan