Posts by nightkil

Re: Update a named range vba

Hello Robert,

your code worked great. thanks.

RoyUK, the reason I had to go with VBA was because the sheet clears everything after row 18 before getting new data. I didn't make it I'm just using it to take data and imported into access.

Thank you all.

Re: Update a named range vba

Good morning,

F18:HA. the column number should never change but it could. however, the row number will change everyday.

Thank you
Ed

Hello,

I'm trying to use a vba to update a named range on sheet2 when new rows are added. I would like the code to find the last row and define the name range called "traffic" to add the new rows.

Thank you in advance.

Re: Open The Find&replace Box

That worked great. Thanks

Hi,

I try searching for this and was not able to find it...

I'm looking to have a Button on my sheet that will open the Find box so people do not have to go to the menu to find it. Is there a code that can open this?

Thank you for your help

Re: Paste A Cell Value To Another Cell With Code

Thank you both. I tried both codes and I was getting some errors so I expanded the field and used the old code and everything seems to be working fine. Since this is not a big deal I might just leave it as is for now and move on to bigger things lol

Thank you both and Maxims thank you for explaining why I was getting the error in the first place.

Re: Paste A Cell Value To Another Cell With Code

Maxims,

Thanks for the reply. I tried the code but it copied the whole row. I'm only looking to get the value from the last cell in column A to L1.

I'm not sure if I need to do or change something else but I'll give it another try

Hello everyone,

I have this code that I have been using fine until today when it just stop working for me... It's a simple copy and paste using VBA Code. The code finds the last row in Column A and copies the cell to L1. Below you will find the code I'm using and the line in yellow is where I'm getting the error.

Private Sub Workbook_Open()


Range("A1").End(xlDown).Select
Selection.Copy
Range("L1").Select
[COLOR="Yellow"]ActiveSheet.Paste[/COLOR]
Range("A1").Select
Application.CutCopyMode = False
UserForm.Show


End Sub

This codes runs as soon as the worksheet opens and it has worked for over a year. I'm using MS Excel 2007. Please let me know what you guys think I have tried pretty much everything I can think of but i dont really know what i'm doing when it comes to VBA.

Thank you

Re: Copy Data From Multiple Sheets To One Master Sheet

StephenR,

That one works great!!. Thank you and David for taking the time to help me with this issue.

Re: Copy Data From Multiple Sheets To One Master Sheet

Lets hope this is the last error lol.

There are some records from "SR3-BK5" sheet that are showing with the "SR2-FC1" sheet name on the Master List. There are other ones like this. I'm leaving now and I not sure when this weekend I will want to see this again but I'll keep my eye out for your response.

Have a good Friday

Re: Copy Data From Multiple Sheets To One Master Sheet

Here is a VERY short version of the file I'm using. I left the results of running the code you posted here and made the records I was talking about yellow (Pasting the data form column A in the Master List Sheet). I hope this helps.

Re: Copy Data From Multiple Sheets To One Master Sheet

StephenR,

In every sheet with data in column B has to have data in the other columns. After a bit more research I notice that the code that Dave posted is only getting the data from column B.

Is not 1 sheet thats the problem it seems to be all sheets (I worded that wrong before).

I hope that helps.

Re: Copy Data From Multiple Sheets To One Master Sheet

Thank you Dave,

After making that change the data that did transfer actually looked pretty good. But now I get a new error.

The error is "Run-Time error '1004': Application-defined or object-defined error".

If I debug it brings me the orange line below.

Sub SummurizeSheets()
    Dim ws As Worksheet
    Dim lastRng As Range
    Dim lastCll As Range
     
    Application.ScreenUpdating = False
    Sheets("Master List").Activate
     
    For Each ws In Worksheets
        Set lastRng = Range("A65536").End(xlUp).Offset(1, 0)
        Select Case ws.Name
        Case "Master List", "Map"
             'do nothing
        Case Else
            Set lastCll = ws.Columns(2).Find(What:="*", After:=ws.Range("B1"), SearchDirection:=xlPrevious)
            ws.Range("B2:" & lastCll.Address).Copy
            Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
             'add sheet name before data
            [COLOR="Orange"]lastRng.Resize(lastCll.Row - 1) = ws.Name[/COLOR]
        End Select
    Next ws
     
    Columns("B").SpecialCells(xlBlanks).EntireRow.Delete (xlUp)
     
    Application.ScreenUpdating = True
     
End Sub

Thank you again.

Actually after looking at the data again I notice I'm only getting the data for column B. I still need to have all the data in B:K. I'm not sure if this is because of the error above.

Re: Copy Data From Multiple Sheets To One Master Sheet

I'm sorry to keep doing this to you. That works better than all the other codes but a new problem came with this one. Now for some sheets its pasting the data from column A when it should be pasting pasting column B.

ie. Sheet 1 to 5 are done correctly (the code takes B:K and puts it correctly in the master list) but for sheet 6 column A was also pasted. I'm not even sure how its getting the data from column A since we're looking for B.

Re: Copy Data From Multiple Sheets To One Master Sheet

that should be correct. I mean at this point I dont think we will reach more than 50.

To answer you other question I have attached a preview of what some of the sheets might look like. We might have data in row1, row5, row10 in one sheet. In other sheets we might have data in rows1-25. So the eisier way to do this (in my mind) was to have a code that takes the first 50 rows on each sheet but then deletes any row that has column B blank. The code you posted works but for some reason the rows are not getting deleted when column B is blank.

I hope I did a better job at explaining it this time.

Thank you for all your help and time.

Re: Copy Data From Multiple Sheets To One Master Sheet

Thats right. The reason is coded that way is because there is some empty rows in between the data in each sheet. But I want to make sure this code gets all that data and puts it in the Master List sheet. Is there a better way to do this?

Thank you.

Re: Delete A Form Text Box With A Macro

That one actually selected the the right form field but I get an error in the statement below

Selection.Delete Unit:=wdCharacter, Count:=1

The error is "this command is not available" "Run-Time Error '4605".

Re: Delete A Form Text Box With A Macro

PeteNZ,

Thanks for trying but didnt work. I try a few things my self but it deletes all the formfields.

ActiveDocument.Unprotect
'Selection.FormFields(text3).Delete
'or
Word.FormField(text3).Delete
ActiveDocument.Protect wdAllowOnlyFormFields

Re: Copy Data From Multiple Sheets To One Master Sheet

StephenR

First let me thank you for your time.

The code worked for the problem explained but now there is another issue. With the code you posted we always have 49 entries for every sheet name in column A.If the sheet does not have 49 records then records from other sheets will be place in those rows. Does that make any sense?

Hello everyone,

I'm looking for help with a code that will delete a form text box in a work doc. The form text box is bookmark as text3. Has anyone done this before?

Thank you