Posts by VBA Noob

Hi all,

I'm looking to use Autofilter and current region to select each persons data and paste to a new sheet.

The trouble is i'm not sure how to change the criteria name in the loop

e.g first name in list is Jack, next is John

Selection.AutoFilter Field:=1, Criteria1:="Jack"

Hopefully someone can point me in the right direction

VBA Noob

Re: Inserting 1 line in sht1 to enter 5 lines in sh2

Quote from Will Riley

If I were you i'd carefully consider the advice being offered here. It comes from people who have already "made all the mistakes" and are happy to try to stop others falling in to the same traps.

We are certainly not wasting your time - we're actually trying to help you not waste your time.

Having looked at your sheet, I'd say that you are asking for big trouble even attempting to do what Excel is not good at. Trying to "link" the data given your current set up will be impossible to lock down to ensure the integrity of your data. I will bet Dave's next adsense cheque that if you manage to build something like this, it will take at most two weeks before a user screws it up for you.

If you want easy data entry for your users, use a userform to capture the data - better yet, use a proper database app to store the data (such as Access) - you can always use XL for front-end data entry & reporting if your users don't have Access.

The whole idea of trying to maintain links between two sheets where the data is held in a completely different format is fundamentally wrong.

Display More

Comments noted .

Thanks

VBA Noob

Re: Inserting 1 line in sht1 to enter 5 lines in sh2

Quote from royUK

Why can't you simply combine the two sheets with columns for Growth, Trend etc on Sheet1/

Sht1 needs to go from left to right to make it easy for the end user to enter the %.

Sht2 will be used to analysis the data in pivots so needs to go top to bottom.

It can all go in one sheet but if I do that no one will use it. It's Need flexiablilty.

Guess we're wasting each other's time here.

VBA Noob

Re: Inserting 1 line in sht1 to enter 5 lines in sh2

Attached is simple example as requested. Sheet1 would have around 120-200 lines that need to feed through to sheet2 for 5 years. E.g One entry in sheet 1 would have 3 x calulations done on it.

Sheet2 then has the info ready for pivot manipulatiuon

Note. Formulas for rev I'm still working on. But it would work like this ....changes in 2007 would use 2006 Rev total

VBA Noob

Re: Inserting 1 line in sht1 to enter 5 lines in sh2

Quote from Will Riley

Can I ask why you need to duplicate the data in another sheet? It sure sounds like unnecessary duplication...

Hi Will,

Sht1 does calculations for 2006-2010 then Sht2 is use to pivot info to see trends etc. A new customer in 2007 onwards need to be added to sht1 and 5 years added to sht2.

Any ideas how to link. Do I need VBA code. to keep rows linked ??

Thanks

VBA Noob

Hi all,

I've got two sheets.

One has around 129 lines for the first years 2006 in sheet1.
The other links to the first 129 rows. Then I've linked the top 129 in sheet2 130 down for 2007 and so on for 5 years.

How would I add a line in Sheet1 then would insert 5 lines in sheet2 keeping the same order.

e.g

Sheet1
Name Rev Year
John £120 2006 Row 5
Jack £150 2006 Row 6

Sheet2 (linked)
Name Rev Year
John £120 2006 Row 5
Jack £150 2006 Row 6
John £130 2007 Row 134
Jack £160 2007 Row 135
John £140 2008 Row 263
Jack £160 2008 Row 264
John £150 2009 Row 392
Jack £170 2009 Row 393
John £155 2010 Row 521
Jack £180 2010 Row 522

The Rev changes by formulas in Sheet 1

Thanks In Advance

VBA Noob

Re: Worksheet Function

Just tried it again and it worked.

Not sure what happened.

Thanks again

VBA Noob.

Re: Worksheet Function

Hi Derek,

Thanks for looking at this for me however when I run the macro I'm getting a run time error 13 when I run it.

Any ideas

Quote from Derk

The folowing seems to be the equivalent.
[vba]Dim r As Range, x As Long
Set r = Cells(2, ActiveCell.Column)
x = Int(((r - 1461) - ( _
(DateSerial(Year(r - (r Mod 7) + 3), 1, 2) - 1461) - _
((DateSerial(Year(r - (r Mod 7) + 3), 1, 2) - 1461) Mod 7) _
) + 5) / 7)[/vba]

Display More

Hi all,

How would I add this formula as a worksheet function with VBA

I can't see INT, MOD or Year in VBA. Also want to change C2 to change to activecell column + row 2.

=INT(((C2-1461)-SUM(MOD(DATE(YEAR(C2-MOD(C2,7)+3),1,2)-1461,{1E+99,7})*{1,-1})+5)/7)

Thanks in advance for your help

VBA Noob

Re: formula to calculate age

Dateif Works for me. How about the below to bring back the year he was born

IF(MONTH(TODAY())>MONTH(A1),YEAR(TODAY())-YEAR(A1),
IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1)),
YEAR(TODAY())-YEAR(A1),(YEAR(TODAY())-YEAR(A1))-1))

Re: SUM IF from another sheet

Or this

=SUMIF('Data Input'!J2:J12,Z4,'Data Input'!K2:K12)+SUMIF('Data Input'!J2:J12,AA4,'Data Input'!K2:K12)

Sumif looks at one condition.

This link might give you some ideas on sumproduct

[post]http://www.xldynamic.com/source/xld.SUMPRODUCT.html#double[/post]

If you drag the info insert absolutes where appropriate.

VBA Noob

Re: Date Range

Try this website. It helped me.

http://www.rondebruin.nl/copy3.htm

VBA Noob

Extracting Data from Workbooks

Hi again,

If anyone else comes across this problem may I suggest you take a look at the attached link. It's helped me

http://www.rondebruin.nl/copy3.htm

VBA Noob

Re: Extracting Data from Workbooks

Norie,

I've set up a spreadsheet called Summary. It has two sheets called Totals T and total R.

It would be run once a week so the old data would need to be deleted.

Thanks again

Extracting Data from Workbooks

Hi Norie,

Recording a macro won't work. I want to paste all the info into "column A" from the 20 different sheets each having around 180-220 entries in "Column M".

I was thinking originally that I should extract the data from closed workbooks but find the attached code which opened them which I assume would be easier.

Sorry for the confusion. Any help would be appreciated.

Thanks

VBA Noob

Hi,

I've got 20 spreadsheets called Week 1 to Week 20 each has two sheets called Wk 42 T and Week 42 R. They are stored in a shared area. I want to Copy all the data in Column M in both sheets and then transfer the data to a summary sheet with a sheet for each.

I've got the below code to open the workbooks but need help with selecting that data per workbook then by sheet and looping through each sheet and pasting in Column A.

Any help greatly appreciated

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False


On Error Resume Next


Set wbCodeBook = ThisWorkbook


    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:\MyDocuments\TestResults"
        .FileType = msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"
        
            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                                  
                 'DO YOUR CODE HERE
                 
                 wbResults.Close SaveChanges:=True
             
                 Next lCount
            End If
    End With
    
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Thanks

VBA Noob

Re: User Log error message

Thanks. However I think I will still have a problem if user's prefix letter isn't "K".
:crying:

Re: User Log error message

Hi Dave,

I did try recording first that but no joy. I think it's something to do with his set-up. I've encountered this problem before so thought someone else might of come across it.

I'm looking for some code which looks to see if the path is a Prefix letter or drive path. If it's a letter it uses " Open ThisWorkbook.Path " otherwise it changes the drive address to the path e.g "k\"

Thanks

VBA Noob

Hi all,

I've got the below code which I use to log users who opens my spreadsheet. It's a Event so runs when the spreadsheet is open with macro enabled.

The spreadsheet is on a shared drive which has the prefix "K". Most users it works find but one user gets a error Message "Path or Drive" error. I got around it by adding "on error" but how do you overcome issues with drive prefixs and the drive address e.g "K" vs "\\CRD2000"

Private Sub Workbook_Open()
    Open ThisWorkbook.Path & "\usage.log" For Append As #1
    Print #1, Application.UserName, Now
    Close #1
End Sub

Thanks in advance

VBA Noob

Re: Copy user selected sheets

Thanks. That worked a treat