Help with VBA to copy and paste from sheets contained in a list
Hi
I am stuck trying to write code for a sequence of copy/paste values that requires reference to a list of worksheets.
The attached contains
1. The Admin sheet with a list of sheets that have to be referenced (the number of sheets in the list changes weekly)
2. Sheets containing the data to be copied in H6:I? this list also changes weekly (sometimes there are blank cells)
3. Summary sheet where the copied values are pasted in C6:D? (the previous weeks list is cleared before the new data is brought across)
Sub CopyDataToSummary()Dim ws As Worksheet, dws As Worksheet, admWs As WorksheetDim lr As Long, alr As Long, i As Long, dlr As LongDim xApplication.ScreenUpdating = FalseSet dws = Sheets("Summary")Set admWs = Sheets("Admin")dws.Cells.Cleardws.Range("C5:D5").Value = Array("Code", "Sales")alr = admWs.Cells(Rows.Count, 2).End(xlUp).RowIf alr < 8 Then MsgBox "There are no sheets listed on Admin Sheet.", vbExclamation, "Sheet List Not Found!" Exit SubEnd Ifx = admWs.Range("B8:B" & alr).ValueFor i = 1 To UBound(x, 1) On Error Resume Next Set ws = Sheets(x(i, 1)) If Not ws Is Nothing Then lr = ws.Cells(Rows.Count, 8).End(xlUp).Row If lr > 5 Then ws.Range("H6:I" & lr).Copy dws.Range("C" & Rows.Count).End(3)(2) End If End IfNext iApplication.ScreenUpdating = TrueMsgBox "Data has been copied to Summary Sheet successfully.", vbInformation, "Done!"End Sub
If you have issue opening the attachment due to a temporary bug in the forum, please save it on your system before opening it otherwise just copy the above code on a standard module like Module1.
You may click the button on the Summary Sheet to run the code. Test-1.xlsm
DAVID131
ASKER
The rapid response is very much appreciated
The code works well
The only question is how do I get only the copied cells' values to be pasted as it currently brings across formulae
Please try this......
Open in new window
If you have issue opening the attachment due to a temporary bug in the forum, please save it on your system before opening it otherwise just copy the above code on a standard module like Module1.
You may click the button on the Summary Sheet to run the code.