Free Microsoft Excel 2013 Quick Reference

Macro to copy only 9 digit numbers from txt file to excel

Hello friends,

I need a macro which would copy only 9 digit numbers from txt file to excel. Thanks in advance if this is possible :D

I am attaching a macro which would extact all 9 digit numbers from a txt file. But the challenge is I get some numbers such as ROTF01361 which is indeed 9 digit but also an alpha numeric number! Can anyone edit my macro and give it to me so that the macro identifies and extracts such numbers too

Thank you

I'm looking for a macro that will copy all 3 digit numbers from col D and paste them to col C. There is lots of other stuff in col D, but I only want the 3 digit numbers. Col length will vary. I have a start - see below.
Any ideas??

Thank you.

Dim i As Long
Dim lastrow as long
lastrow = Cells(RowsCount, 4).End(xlUp).Row
For i = 6 To lastrow    
end if
Next i

I'm trying to find a better (smarter) way of extracting a 9 digit number from a string.
The number can start anywhere within the string, and the string may contain other numbers (though not immediately adjacent to the 9 digit number).

I wrote a clumsy macro to text each character (from left to right) to see if it is a number (using a 'if variable >=chr(48) and variable <=chr(57) test) and then tested each of the following 8 characters. Once I established that I had a group of 9 numbers in the string then put the 9 numbers into an adjacent cell.
Then looped through the column with the strings.

I'm thinking there is a better way to do this and any suggestions / pointers most appreciated.

anyone know of a macro to copy only visible cells in a range?


I have a macro which copies 9 digit numbers from a txt file as well as alpha numeric numbers from a txt files. But the problem is it only copies numbers till 4 alphabetic. I am attaching both excel macro as well as txt file. I need all the numbers to get copied. Thanks a lot!

Hi All,

Please correct the attached code where it refers to B2:E500. What I require is the macro to copy the data from the first visible row after filtering for the above range.First visible row is variable depending on selected criteria.

Rest of the macro is working fine.

Thank you,

Sub MOEdatanew_1()

If Application.WorksheetFunction.CountIf( _
    Range("F1:F500"), Range("R1")) > 0 Then
MsgBox "Region Not Found For This Location Number"
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Front Page ").Select
    Exit Sub
End If

On Error Resume Next
With Range("A1:M500")
    .AutoFilter Field:=6, Criteria1:=Range("R1").Value
    VisibleRow = .Offset(1).SpecialCells(xlCellTypeVisible).Row
    Set OutRange = Cells(VisibleRow, 1)
    Application.CutCopyMode = False
    Selection.Copy    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Front Page ").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Input Data").Select
    Application.CutCopyMode = False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Front Page ").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End With
On Error GoTo 0

End Sub


Having searched the forums, and not finding exactly want I need, I wonder if anyone could advise. Apologies in advance for being a Visual Basic novice.

I would like to employ a macro to copy particular rows of data from each of 4 sheets to 4 consecutive rows on a fifth sheet.

Ideally, I would like to run the macro on the first sheet, possibly after highlighting a desired row, and for it to find and copy the same numbered rows from each of the 2nd, 3rd and 4th sheets, pasting them onto the fifth sheet (this may not the most efficient method). So, for example and picking an arbitrary number, row 31 on sheets 1, 2, 3 and 4 are copied/pasted to rows 1, 2, 3 and 4 on the 5th sheet.

I would like to be able to choose the row number (always the same) to be copied. I also need to repeat the process with other rows, copying/pasting over the previously pasted data (I've printed in the mean time).


My original macro looked like this:

Sheets("Inv_Load to Lawson").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Workbooks.Open Filename:= _
"X:Legal to Accounting Check RequestsSales TaxslsTax dbapcvi.csv"

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Then someone suggested I try this one - but neither of them work......I am
trying to copy a range of data from one file to another...should be simple
but I can't get it to work!?!?

Sheets("Inv_Load to
Lawson").Select.Range("A1").End(xlToRight).End(xlD own).Copy
Workbooks.Open Filename:= _
"X:Legal to Accounting Check RequestsSales TaxslsTax dbapcvi.csv"

ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Hi everyone again. I am here looking for your help on further develop this macro to copy data from one workbook to another. This macro, from the help of arlu1201, can copy data from one workbook to another. However, I have tried for many days to make this macro to copy values and not formulas or links. All the data on “Stats” sheet should be copied as values to workbook report2012.xls. I have used .PasteSpecial or Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This is the macro the I am currently using:
Option Explicit
Dim MFile As String
Dim lastrow As Long
Dim frow As Long
Dim lrow As Long

Sub update_master()

MFile = "C:KBD projectreport2012.xls"

If MFile = "" Then
    MsgBox "Please open the KBD Shop Floor Reports 2012", vbCritical
End If
Workbooks.Open MFile
MFile = ActiveWorkbook.Name

lastrow = ThisWorkbook.Worksheets("Stats").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("Stats").Range("A2:V" & lastrow).Copy _
Workbooks(MFile).Worksheets("Libros").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Workbooks("report2012.xls").Close SaveChanges:=True
End Sub
Could someone explain to me what I am doing wrong? I am very new to VBA, but I am willing to work hard and learn. See attachment for detail.s
Again, thank you everyone, specially my friends for your time and knowledge.

Here is the job I want this macro to do:
1) current sheet is named "Home"
2) a button is created to assign the macro
3) When I click the button, I want the macro to copy a range of cells from one of many other sheets (A1:L19) to the current sheet (starting at cell C58) based on the value of a drop-down cell C6. The sheets are named after the drop down list, but not all values have sheets.

Conditions: When the use chooses a value from the drop-down cell C6, because some values of the drop down list don't have a corresponding worksheet, so when the user choose those values, a msg box of "No data available" should pop out when the user click the button. To do so, I use another cell L36 to indicate Yes or No. If L36 is "Yes", then data will be copied over when the user hit the button, and vice versa. Another point is because the value of the drop-down cell C6 could be blank, when the user select blank value, then a msg box showing "please select a product first" should pop out. After the user does select a product, and click the button, data should be copied over.

Below is the code I use:


Sub CopyFeatures()
' CopyFeatures Macro
    If Sheets("Home ").Range("L36").Value = "No" Then MsgBox ("No data available")
       Exit Sub
    Dim sheetName As String
    sheetName = Sheets("Home ").Range("C6")
    If sheetName = "" Then MsgBox ("Please Select a Wolfson Part")
        Exit Sub
        Sheets("Home ").Select
End Sub
It works if I choose a product and the value of cell L36 is yes, but in any other case, the macro will just stop functioning. I guess I probably use wrong Exit Sub, but not sure how to correct. Any help will be appreciated.


i'm trying to write a macro to copy a row from sheet 1 to sheet 2, but the row can be varied by the user. in other words they can select row 15 or row 35 or any other. i can do the copy and paste bit but cant figure out how to get the macro to copy only the selected row. can anyone help please

Hello, I am attempting to create a macro to copy and paste values, information from all worksheets in my workbook (except my summary sheets). Here is the code I am using, but so far, it hasn't worked.
Public Sub CopyandPaste() 
    Dim ws As Worksheet
    For Each ws In Worksheets 
        If ws.Name <> "Paste" and
		ws.Name <> “Total International” and
		ws.Name <> “Summary by Region” and
		ws.Name <> “Summary by Country” and
		ws.Name <> “Summary by Entity” 
            With ws.Range("a24"). CurrentRegion 
                With .Resize(.Rows.Count - IIf(flg = True, 1, 0)) 
                    Worksheets("Paste").Range("A2:W2500").End(xlUp).Offset(1). _ 
                    Resize(.Rows.Count, .Columns.Count).Value = .Value 
                    flg = True 
                End With 
            End With 
        End If 
	End If
	End If
	End If
	End If	
    Next ws 
End Sub
Ideally, I would like a macro that would be able to copy and paste multiple ranges of rows (i.e. each tab in my workbook contains a different number of rows that actually contain information I want). On each worksheet, the data I want begins on row 24, and I have the word "end" typed in the first cell of the row after my relevant data ends. So, ideally I would want to copy and paste from row 24 until one row short of the word "end".

Any help you guys can provide would be MUCH appreciated.

Hi guys,

i have a cell filled with data (don't ask!) including multiple 7 digit numbers, is there a way to extract all 7 digit numbers from the cell? I've attached the document, and if it helps at all, all the 7 digit numbers start with either a 5,7 or 9.

What I'm trying to do is, I want to remove only the first numbers from a mixed cell, example:

Cell A2 states:
23 pkts x 20 gms
Cell A3 States:
440 cans x 200 ml

I want Cell C2 to say:
AND C3 to say:

I'm using this formula by the way, but its not what i actually wants..
=LEFT(E7,FIND("^^",SUBSTITUTE(" "&E7," ","^^",LEN(" "&E7)-LEN(SUBSTITUTE(" "&E7," ",""))))-1)

I have a sheet with data in several columns and in the first 1000 or so rows is there a macro to copy just these filled rows and then paste to another sheet at the end of the filled rows in that sheet. Thanks in advance for any help

the data is like this:

Col A Col B

30 X
60 X

i would like a macro to copy only 30 and 60 because there is an "X" against them in column B.

i would like the macro to copy this values on the clipboard or a list with no blanks..

can anyone help? thanx

Just curios if this is the most efficient way to copy a workbook x number of times. I tried copying 77 workbooks and not sure exactly how long it took, but about 2 mintues. The original workbook is 300 KB.

Is there anything that can be done to speed the process up a bit?

Sub CopyBook()
    Dim I As Integer
    I = InputBox("Enter number of workbooks to create?")
    For I = 2 To I
        ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & CStr(I) & ".xls"
    Next I
End Sub

Is is possible for a macro to copy a range of cells from one file to the same
range in another file? The two files would be open; but the file names may
change. The recorder records absolute ranges correctly, but hard codes the
file names. Thanks for any help.

Right, I'm not sure where to start. It is probably good idea to check the attached file. I have a spreadsheet where I would like to extract some data. Namely I'm after numeric values that are exactly 9 digits. The information is spread across the sheet and I would like to get a list on numbers in Column A on sheet called "output". What makes it harder is that the numbers are within merged cells and there are rows of irrelevant data between the numbers. Positive side is that there is system or pattern.
The data should be extracted from the columns below (last row as per last used cell of column C):
Or just maybe the whole area AH9:IE(last row used) can be looked at in one go, and only the 9 digit numbers to be copied

The data should go to sheet "Output" and should be in A1:A (no header). Example sheet Output (After) is included.

if anyone feels up for the challenge and would like to help me out, I would be very thankful. I am getting better at things and I have created some wonderful macros at my work place but there is still so many things that I don't know how to do.


hi everybody,

There is a need to validate a cell to insert only from 9 digit number to 13
digit number.

For Example: 000000001 to 9999999999999. ( I want to ristrict the
user to enter even 8 digit number i.e. 99999999)



I am preparing a macro which identifies and copies 9 digit alpha numeric code (alpha till 4). The code am using is "bw{0,9}d{4,9}b"

But this code is copying alpha numeric numbers more than 9 too.....

I need to prepare a macro which would take only 9 digit numbers (including alphabets till 5)

My full macro code is
Sub ExtractNumbers()

Dim Filename As String
Dim Filepath As String
Dim Filespec As String
Dim Match As Variant
Dim RegExp As Object
Dim R As Long
Dim Rng As Range
Dim TextFile As Integer
Dim TextLine As String
Dim Wks As Worksheet

' Output worksheet and starting cell.
Set Wks = Worksheets("Sheet2")
Set Rng = Wks.Range("A1")

' Location and name of text file.
Filename = "Test.txt"
Filepath = "C:Documents and Settingsrra172Desktop"

Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Global = True
RegExp.Pattern = "bw{0,9}d{4,9}b"

' Add backslash to the end of the file path if needed.
If Right(Filepath, 1) <> "" Then
Filespec = Filepath & "" & Filename
Filespec = Filepath & Filename
End If

TextFile = FreeFile

' Parse the 9 digit numbers from the text file and output them to the worksheet.
Open Filespec For Input Access Read As #TextFile
Do While Not EOF(TextFile)
Line Input #TextFile, TextLine
If RegExp.test(TextLine) = True Then
For Each Match In RegExp.Execute(TextLine)
Rng.Offset(R, 0).Value = Match
R = R + 1
Next Match
End If
Close #TextFile

End Sub
I am attaching the txt file from which this macro should copy the numbers. thanks

I need an help to create a macro to copy the value from number of excel sheets and paste it into another single excel sheet.
I have number files which are located in one location (ex: test, test1, test2, test3, test4)
I need to open each file and copy the item number which is in Cell B2 in the file named test.xls and paste it in the FINAL workbook and then again copy Total_Liab value in the tab named Calculations in the same workbook test.xls (total liability value) which is the last cell value in the column Q (example: Q51 in the Calculations sheet) Q51 and paste it next to the same item number in the FINAL workbook. Once it perform the action it should close the test.xls worksheet and then open the next test1.xls worksheet and should follow the same procedure as explained for the test.xls. Could you please help to create an macro

Sorry ppl for the earlier username,it was an innocent one didnt mean to offend u;ll.

Hi if anyone could help me with ways to how to identify only 2 digit permuted number from list of 3 digit number using excel .

Let say we have this set of number (2 Digit) number 35,23,54,44 and (3 digit) number 135,253,145,445.

I need a formula to identify or track if the 2 digit numbers(each one of that 2 digit) is in the 3digit number.

For example
35 is in 235 and 253 (note that digit 3,digit 5 can be in anywhere in that specific 3 digit number)

Same goes to

23 is in 253

54 is in 145

44 is in 445

Thank you

PS:Hope Stan can help me again.Thank you.

This one may be quite a challenge, if it is at all possible.

First I would like to say that this website is definetely the best information source for Exel help I have been able to find anywhere.

What I am trying to do is a little difficult to explain, so please bear with me.

I recieve form e-mails from my customers that includes contact information, and most importantly a phone number that needs to be pre-qualified for DSL service.

The format is below:

streetaddress : XXXXXXXXXX
state : XX
zip : XXXXX
areacode :XXX
phone1 : XXX
phone2 : XXXX
contact_time : M-F, 5-6pm
areacode2 : XXX
phone12 : XXX
phone22 : XXXX
email :
Submit : Go

Option 1

I need a macro to open these e-mails, and copy (select all and copy) the information into Excel with the "Name", "Street address"... in column A, and the "XXXXXX" (Customer input information) in column B.

Then I need an Excel macro that will copy only the numbers in column B next to "Area code", "phone1", and "phone2", and paste it into a field in a webpage that I can specify.

Option 2

May be easier, (once again, if possible)

If the macro can copy only the customer input (XXX) after "Areacode", "Phone1", and "phone2", and paste it as a continuos line (no spaces) into the field in the web page I need to use.

I know that this one is a pretty "tall order," if it were easy I probably could figure it out on my own, or maybe I am just giving myself too much credit.

Thank you very much to whomever may help; this will help save me hours of work, and my company the cost of those hours of work.