• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

selecting sheet to consolidate

Y

ysherriff

Member
good evening all. I have a code that was created and it works perfectly. the only modification, i need is the ability to select the sheet I would like to consolidate. let me give you a brief background.

i have multiple workbooks by state with 12 tabs in each workbook. the tabs are the same because it is based off template. the tabs are named "Week 1", "Week 2", "Week 3", until "Week 12"

Each week, i have to consolidate that weeks data and then do some complicated lookups.

The vba works perfectly but there is a code in my macro where i have to activate that week tab by changing this line" Sheets("week 1").Activate" . I am wondering is there a way for me to create a userform that will ask me which sheet I would like to consolidate and then have it automatically consolidate that sheet from all the workbooks.

I have attached the workbook. the name of the macro is called "consolidate macro"

I have also copied the entire macro below" thank you for all your help.

------
Sub mod_consolidate()
Dim strListSheet As String, sh As Worksheet, TargetSh As Worksheet
Dim DestCell As Range, LastRow As Long, i As Integer, strFileNamePath As String
Dim strFileName As String, currentWB As Workbook, dataWB As Workbook, filecount As Integer
Dim prctProgress As Single

strListSheet = "Report File Path"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

filecount = Range("FILE_COUNT_LEVEL") ' the number of files to consolidate

On Error Resume Next
Set TargetSh = Worksheets("Master")
On Error GoTo 0

Sheets("Master").Activate
Rows("2:" & Rows.Count).ClearContents

Set DestCell = TargetSh.Range("A1")
Set DestCell = DestCell.Offset(1, 0)

'On Error GoTo ErrH
Sheets(strListSheet).Activate
Range("b2").Select

ProgressBox.Show 'displays progress bar

'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
For i = 1 To filecount

strFileNamePath = Range("strFileName").Offset(i, 1)
strFileName = Range("strFileName").Offset(i, 0)

'this displays the status in percentage value in the progress bar of the PSR file being generated and the name of file
'being generated
Application.StatusBar = "Generating " & strFileName & " Consolidation....." & i & " of " & filecount

prctProgress = i / filecount * 100

ProgressBox.Increment prctProgress, "Consolidating for " & strFileName & "- " & i & " out of " & filecount


Application.Workbooks.Open strFileNamePath, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
ActiveSheet.Unprotect "ops"
Set sh = ActiveSheet
ActiveSheet.AutoFilterMode = False

'select usedrange to copy
Sheets("week 1").Activate
LastRow = ActiveSheet.Range("B55").End(xlUp).Row
If LastRow > 1 Then
sh.Range("B7:O" & LastRow).Copy

'activate generator workbook
currentWB.Activate

'activate master worksheet
TargetSh.Activate

TargetSh.Range(DestCell.Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DestCell = DestCell.Offset(LastRow - 6)
dataWB.Close False
End If



Next i
Application.StatusBar = False
ProgressBox.Hide
currentWB.Activate
Sheets("Master").Activate
ActiveSheet.UsedRange.EntireColumn.AutoFit 'AutoFit the column width
Columns("E:E").Select
Selection.Style = "Percent"
MsgBox "Reports have been generated succussfully!", vbInformation

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

Attachments

  • Weekend PCF Planning Generator v.04.xls
    184.5 KB · Views: 5
You can add something like this in your code
Dim sheetname As String
sheetname = InputBox("Sheet name to be selected")
Sheets(sheetname).Activate
 
Hi YSherrif

This process looks like it could be streamlined probably with 25-30 less lines to do the same thing.

The simplest method in order to consolidate a certain sheet from your files would be to create a list lets call this list Weeks, which has all the weeks in the year. Add a data validation which points to the Weeks and choose the week. Then hit the button which runs your coding.

Take care

Smallman
 
it works but the only thing is the code always ask for the sheetname. how can i place it outside of the loop so i don't have to continuously type in the name every time it loops.

i will see if there is a solution
 
smallman.. one modification. i have the code but the only hiccup is that it keeps consolidating the first sheet even though i changed the data validation to another sheet. where can i possibly be going wrong with the modified code. i have attached the updated file.
below is the full code with red being the modification:

------------------------------
Sub mod_consolidate()
Dim strListSheet As String, sh As Worksheet, TargetSh As Worksheet
Dim DestCell As Range, LastRow As Long, i As Integer, strFileNamePath As String
Dim strFileName As String, currentWB As Workbook, dataWB As Workbook, filecount As Integer
Dim prctProgress As Single, sheetname As String

strListSheet = "Report File Path"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

filecount = Range("FILE_COUNT_LEVEL") ' the number of files to consolidate


On Error Resume Next
Set TargetSh = Worksheets("Master")
On Error GoTo 0

Sheets("Master").Activate
Rows("2:" & Rows.Count).ClearContents

Set DestCell = TargetSh.Range("A1")
Set DestCell = DestCell.Offset(1, 0)

'On Error GoTo ErrH
Sheets(strListSheet).Activate
Range("b2").Select

ProgressBox.Show 'displays progress bar

'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
For i = 1 To filecount

strFileNamePath = Range("strFileName").Offset(i, 1)
strFileName = Range("strFileName").Offset(i, 0)
sheetname = Range("WEEK_SELECTION")

'this displays the status in percentage value in the progress bar of the PSR file being generated and the name of file
'being generated
Application.StatusBar = "Generating " & strFileName & " Consolidation....." & i & " of " & filecount

prctProgress = i / filecount * 100

ProgressBox.Increment prctProgress, "Consolidating for " & strFileName & "- " & i & " out of " & filecount


Application.Workbooks.Open strFileNamePath, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
ActiveSheet.Unprotect "ops"
Set sh = ActiveSheet
ActiveSheet.AutoFilterMode = False

Sheets(sheetname).Activate

'select usedrange to copy
LastRow = ActiveSheet.Range("B55").End(xlUp).Row
If LastRow > 1 Then
sh.Range("B7:O" & LastRow).Copy

'activate generator workbook
currentWB.Activate

'activate master worksheet
TargetSh.Activate

TargetSh.Range(DestCell.Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DestCell = DestCell.Offset(LastRow - 6)
dataWB.Close False
End If


Next i
Application.StatusBar = False
ProgressBox.Hide
currentWB.Activate
Sheets("Master").Activate
ActiveSheet.UsedRange.EntireColumn.AutoFit 'AutoFit the column width
Columns("E:E").Select
Selection.Style = "Percent"
MsgBox "Reports have been generated succussfully!", vbInformation

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

Attachments

  • Weekend PCF Planning Generator v.04.xls
    185.5 KB · Views: 7
Hi Ysherriff

Just looking at your code it looks like this is the problem:

sh.Range("B7:O" & LastRow).Copy

Where you need to put:

sheets(sheetname).Range("B7:O" & LastRow).Copy

Where Sheetname is the name of the variable in Range("WEEK_SELECTION")

This really occurred because your sheet variable Sh is declared as the activehsheet and there is no guarantee that the file will open on the Week number you are looking for.

Should solve that problem.

Take care

Smallman
 
You must log in or register to reply here.
Back
Top