PDA

View Full Version : help needed for column retrieval from various sheets.



tanglin10
01-22-2013, 05:08 PM
I'm having a group of worksheets that are dated according to the dates in a month , each individual sheet counts the workers that are present for that day for a particular skill in that site is there a way to automatically update the master sheet by using this column (applies for all sheets) . I can load the excel file if this is feasible . :help

tanglin10
01-22-2013, 05:51 PM
I'm having a group of worksheets that are dated according to the dates in a month , each individual sheet counts the workers that are present for that day for a particular skill in that site is there a way to automatically update the master sheet by using this column (applies for all sheets) . I can load the excel file if this is feasible . :help



Currently using this feature
Option Explicit

'Note: This example use the function LastRow
'This example copy the range A2:G2 from each worksheet.
'
'Change the range here
'
''Fill in the range that you want to copy
'Set CopyRng = sh.Range("A2:G2")

'When you run one of the examples it will first delete the summary worksheet
'named RDBMergeSheet if it exists and then adds a new one to the workbook.
'This ensures that the data is always up to date after you run the code.

'*****READ THE TIPS on the website****

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

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

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets except the RDBMerge worksheet and the
'Information worksheet, you can ad more sheets to the array if you want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0)) Then

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A2:G2")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below this macro
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

tanglin10
01-22-2013, 07:04 PM
column K values for the various dates of a month need to be appended automatically into the main sheet labelled as "raw data" can anyone help its kinda difficult as I just started learning VBA .

Download to Excel File :
http://minus.com/l4hu7Pdx3FbQa