Posts by biggree

biggree · May 6th 2013

Re: Pivot table: filter dates and group by year

the below will explain what you need to do...

http://support.microsoft.com/kb/178767

biggree · May 6th 2013

Re: Insert data from different worksheet based on common value

if you dont want to use macros then you will need to manually paste the information into a tab. attached is an example.

biggree · Apr 24th 2013

Re: Run time error - 9

Thanks its working now...

biggree · Apr 24th 2013

Re: Run time error - 9

this is file where i need the data to pasted into.

hi Roy, yes i did and it works fine if the tab name was same across all files. but the file user would select could have only 1 tab which i want or all 5, but i need to paste the data in respect tabs only. Hope i make some sense?

biggree · Apr 24th 2013

Re: Run time error - 9

thanks cytop but i am still getting error on below line:

Code

sht.Range("A1:CQ500").Copy Destination:=wsMaster.Sheets(sht.Range("A4").Value).Range("A" & r).Offset(1, 0)

Also the tab names could be MC or NSW or QLD or VIC or TOT any of the 5.

biggree · Apr 24th 2013

Re: Run time error - 9

the error is errors in any of the below, when it fails to find the tab name.

Code

[COLOR=blue]If[/COLOR] Sheets("MC").Range("A4") = "MC" [COLOR=blue]Then[/COLOR]                     xlsFiles.Sheets("MC").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("MC").Range("A" & r).Offset(1, 0)                                      [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]                                  [COLOR=blue]If[/COLOR] Sheets("NSW").Range("A4") = "NSW" [COLOR=blue]Then[/COLOR]                     xlsFiles.Sheets("NSW").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("NSW").Range("A" & r).Offset(1, 0)                 [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]                                  [COLOR=blue]If[/COLOR] Sheets("QLD").Range("A4") = "QLD" [COLOR=blue]Then[/COLOR]                     xlsFiles.Sheets("QLD").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("QLD").Range("A" & r).Offset(1, 0)                 [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]                                  [COLOR=blue]If[/COLOR] Sheets("VIC").Range("A4") = "VIC" [COLOR=blue]Then[/COLOR]                     xlsFiles.Sheets("VIC").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("VIC").Range("A" & r).Offset(1, 0)                 [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]                                  [COLOR=blue]If[/COLOR] Sheets("TOT").Range("A4") = "TOT" [COLOR=blue]Then[/COLOR]                     xlsFiles.Sheets("TOT").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("TOT").Range("A" & r).Offset(1, 0)                 [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]

biggree · Apr 24th 2013

Re: Run time error - 9

its refers to book which it will open.

the file where i want the information be pasted is called Summary and user will select random file name.

biggree · Apr 24th 2013

Hope someone can help me, I have the below code (got from few website and modified) which for some reason its not running the code and giving me an error message.

to Summarize what I am doing, I want the user be able to select files which have extension of XLSX or XLSM only. And then open file find the tab name which is either MC, NSW, VIC, QLD, TOT and paste the information in respective tab on my current file which also has the same tab.

the file the user select can have one or more tab names and not necessarily will have them all which is MC, NSW, VIC, QLD, TOT and will also have some extra tabs as well which I am not interested in.

Code

Sub Button1_Click()
Dim wsMaster As Workbook, xlsFiles As Workbook
Dim WS As Worksheet
Dim Filename As String
Dim File As Integer
Dim r As Long
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
     
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Title = "Select files to process"
        .Show
         
        If .SelectedItems.Count = 0 Then Exit Sub
         
        Set wsMaster = ActiveWorkbook
         
        For File = 1 To .SelectedItems.Count
             
            Filename = .SelectedItems.Item(File)
             
            If Right(Filename, 5) = ".xlsx" Or Right(Filename, 5) = ".xlsm" Then
                 
                Workbooks.Open Filename, 0, True
                 
                Set xlsFiles = ActiveWorkbook
                 
                r = 1
     
     
     If Sheets("MC").Range("A4") = "MC" Then
                   xlsFiles.Sheets("MC").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("MC").Range("A" & r).Offset(1, 0)
     
     End If
     
        If Sheets("NSW").Range("A4") = "NSW" Then
                xlsFiles.Sheets("NSW").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("NSW").Range("A" & r).Offset(1, 0)
     End If
     
        If Sheets("QLD").Range("A4") = "QLD" Then
                xlsFiles.Sheets("QLD").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("QLD").Range("A" & r).Offset(1, 0)
     End If
     
        If Sheets("VIC").Range("A4") = "VIC" Then
                xlsFiles.Sheets("VIC").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("VIC").Range("A" & r).Offset(1, 0)
     End If
     
        If Sheets("TOT").Range("A4") = "TOT" Then
                xlsFiles.Sheets("TOT").Range("A1:cq500").Copy Destination:=wsMaster.Sheets("TOT").Range("A" & r).Offset(1, 0)
        End If
        
                 xlsFiles.Close SaveChanges:=False 'close without saving
        




        End If
          
        Next File 'go to the next file and repeat the process
         
    End With
     
    Set wsMaster = Nothing
    Set xlsFiles = Nothing
     
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
         
    End With




End Sub

Display More

biggree · Apr 22nd 2013

Re: Calculating time slots and the midnight problem - possibly

yes you will out by one second... there is way to cover it if i get time i will try to give you a formula for it...

biggree · Apr 22nd 2013

Re: Calculating time slots and the midnight problem - possibly

just change the cell E2 to 12:00:01 AM

biggree · Apr 18th 2013

Re: Remove Sort from Ribbon and from Filter Menu

i think this should help you?

http://office.microsoft.com/en…ing-data-HA010222142.aspx

biggree · Apr 17th 2013

Re: Returning a date

i think this is what you are looking for:

http://www.ozgrid.com/forum/showthread.php?t=106105

biggree · Apr 17th 2013

Re: Choose a supervisor from a dropdown and return team roster on multiple cells

why don't you just use pivot attached is a spreadsheet as example.

biggree · Apr 16th 2013

Re: conditional formatting if date equals next month

highlight the column you want this to formatted and use formula as =MONTH(F1)>MONTH(TODAY()) as your condition.

biggree · Apr 16th 2013

Re: find match cell from 2 sheets and copy cells from one to another

why don't you just use vlookup instead of VBA?

=VLOOKUP(A1,SheetB!A:B,2,FALSE)

biggree · Apr 9th 2013

Re: Formula for Turnaround time, using dates and times

its one minus the other and then change the text format to D H:MM

biggree · Apr 9th 2013

Re: Control PivotTable Filter with VBA using LEFT command

look at the below link it will be helpful;

http://www.mrexcel.com/forum/e…box-you-type-textbox.html

biggree · Apr 9th 2013

Re: Merging One Sheet from Multiple Workbooks into One Master

try the below code:

Code

Sub Button1_Click()
Dim wsMaster As Workbook, xlsFiles As Workbook
Dim Filename As String
Dim File As Integer
Dim r As Long
 
r = 0
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Title = "Select files to process"
        .Show
        
        If .SelectedItems.Count = 0 Then Exit Sub
        
        Set wsMaster = ActiveWorkbook
        
        For File = 1 To .SelectedItems.Count
            
            Filename = .SelectedItems.Item(File)
            
            If Right(Filename, 4) = ".xls" Or Right(Filename, 5) = ".xlsm" Then
                
                Workbooks.Open Filename, 0, True
                
                Set xlsFiles = ActiveWorkbook
                
                r = wsMaster.Sheets("Sheet2").UsedRange.Rows.Count
                
                xlsFiles.Sheets("BOB").Range("A1:L65000").Copy Destination:=wsMaster.Sheets("Sheet2").Range("A" & r).Offset(1, 0)
                
                xlsFiles.Close SaveChanges:=False 'close without saving
                
            End If
            
        Next File 'go to the next file and repeat the process
        
    End With
    
    Set wsMaster = Nothing
    Set xlsFiles = Nothing
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        
    End With
    
End Sub

Display More

biggree · Apr 9th 2013

Re: Formula for Turnaround time, using dates and times

hope this helps

biggree · Apr 5th 2013

Re: Count Repeating cell value within a range

try the attached file.