Free Microsoft Excel 2013 Quick Reference

Copy values between sheets

Hi,
I have a workbook (Wb1) which copy some data from sheet2 to sheet1, and the data copied to sheet1 is used to create some charts. By pressing a button on sheet 2 a macro i executed that copy sheet1 into a new workbook, make some changes, save it as a specific filename (Wb2) and closes the newly saved workbook. The problem is that the data in Wb2 still are linked to Wb1.sheet2 which is not an option in this case. To copy sheet1 into a new workbook i use sheets("sheet1").copy and I neet to complete it with something like the paste special (paste only value) function.

Thanks

Finno


I have an excel file with several sheets. I want to copy values on
sheet 1 using a macro command because I have several values to copy
each day. One value of interest is located on sheet 1 at range b11.

On sheet 2 I have 2 columns, one column called -ind1- returns a '1'
when todays date is entered. The other column called -TSCRAL- is where
I want the value from Sheet 1 pasted (at the intersect of the returned
value 1 of 'ind1' column depending on the date)

--
bbuzz
------------------------------------------------------------------------
bbuzz's Profile: http://www.excelforum.com/member.php...o&userid=28466
View this thread: http://www.excelforum.com/showthread...hreadid=480810

I have an excel file with several sheets. I want to copy values on sheet 1 using a macro command because I have several values to copy each day. One value of interest is located on sheet 1 at range b11.

On sheet 2 I have 2 columns, one column called -ind1- returns a '1' when todays date is entered. The other column called -TSCRAL- is where I want the value from Sheet 1 pasted (at the intersect of the returned value 1 of 'ind1' column depending on the date)

I use Excel 2008 for Mac.

I'm looking for a consistent way to automatically copy formulas between sheets in a workbook, and between separate files. I know about Paste Special but using this feature adds a lot of time to my day. I would like this to work with just control-C.

It seems to work about 80% of the time but then "randomly" at other times it just pastes the values. This is really annoying. Anyone have any insight?

Hello! I am trying to copy values between two worksheets. On the
second worksheet I want to allow users to be able to see scenarios asa
the numbers change. So when the user clicks a command button for the
first copy we have original values. For the second copy the values
will be different. Yet I am having a problem that I can't seem to
capture only the value. I read that PasteSpecial should do the trick
.... yet I am using variables to pass the columns that I happen to be
working with.

With the following I get an error. Any tips or clues? Thanks in
advance.

Worksheets("Sheet1").Cells(5,12).Copy
Sheets("Sheet2").Range(Cells(8, EmptyColumn), Cells
_(8,EmptyColumn+1).PasteSpecial Paste:=xlPasteFormats
Sheets("Sheet2").Range(Cells(8, EmptyColumn), Cells
_(8,EmptyColumn+1).PasteSpecial Paste:=xlPasteValues

I am trying to compare cell values between sheet but I keep get a "subscript out of range" error


	VB:
	
 compareSheets(Sheets("shippablegoodspriorday"), Sheets("notfound"), Sheets("variance"), Sheets("shippablegoods"),
Sheets("shippablegoods").Range("A:A"), Sheets("shippablegoodpriorday").Range("A:A"), 11, 11) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
) 
     
    WS1.Activate 
    For Each d In col1 
         'For i = 0 To UBound(goods, 2)
         '    pItemNumber = goods(0, i)
         '    pPrice = goods(1, i)
        found = False 
        With WS3 
            For Each c In col 
                If c.Value = d.Value And c.offset(0, offset).Value = d.offset(0, offset1).Value Then 
                    found = True 
                    c.EntireRow.Delete 
                    Exit For 
                ElseIf c.Value = pItemNumber Then 
                    found = True 
                     
                    If c.offset(0, offset).Value < pPrice Then 
                        WS2.Activate 
                        ActiveCell.EntireRow.Insert 
                        WS2.cells(1, 1).Value = pItemNumber 
                        WS2.cells(1, 2).Value = pPrice - c.offset(0, offset).Value 
                        c.EntireRow.Delete 
                         
                        WS3.Activate 
                    Else 
                        WS2.Activate 
                        ActiveCell.EntireRow.Insert 
                        WS2.cells(1, 1).Value = pItemNumber 
                        WS2.cells(1, 2).Value = c.offset(0, offset).Value - pPrice 
                        c.EntireRow.Delete 
                         
                        WS3.Activate 
                    End If 
                    Exit For 
                End If 
            Next 
             
             'check against the not found sheet
            If found = False Then 
                WS.Activate 
                For Each c In [A:A] 
                    If c.Value = pItemNumber And c.offset(0, 1).Value = pPrice Then 
                        found = True 
                        c.EntireRow.Delete 
                        Exit For 
                    ElseIf c.Value = pItemNumber Then 
                        found = True 
                         
                        If c.offset(0, 1).Value < pPrice Then 
                            WS2.Activate 
                            ActiveCell.EntireRow.Insert 
                            WS2.cells(1, 1).Value = pItemNumber 
                            WS2.cells(1, 2).Value = pPrice - c.offset(0, 1).Value 
                            c.EntireRow.Delete 
                             
                            WS3.Activate 
                        Else 
                            WS2.Activate 
                            ActiveCell.EntireRow.Insert 
                            WS2.cells(1, 1).Value = pItemNumber 
                            WS2.cells(1, 2).Value = c.offset(0, 1).Value - pPrice 
                            c.EntireRow.Delete 
                             
                            WS3.Activate 
                        End If 
                    End If 
                Next 
            End If 
             
            If found = False Then 
                WS.Activate 
                ActiveCell.EntireRow.Insert 
                WS.cells(1, 1).Value = pItemNumber 
                WS.cells(1, 2).Value = pPrice 
                 
                WS3.Activate 
            End If 
        End With 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post;

bump*

If any as any suggestions on methods or ideas I'd greatly appreciate it. I am attempting to copy data between sheets on the first sheet I have two columns one is a list of people and the other is a location. I have a drop down for the location. What I am trying to accomplish is when the location is selected it, the name and location will populate on a different sheet sorted alphabetically with the location below it.
Thanks in advance for any help. I have not been able to develop a viable solution on my own.

Hi All,

I am new to VBA and this forum, but have already learned a lot since joining!

I think there are a few ways to do what i'm trying to do and my first way may not be working. I want to copy/paste between different sheets based on a value in the row. For example, if I have these rows:

Company New/Exist Communication Status
Business1 New No contact
Business2 New No contact
Business3 New No contact

And then I update the communication status (which will happen for multiple rows), then I want to move it to a different sheet. The columns do not exactly align, so I'd paste each column at a time. There are also two other sheets, for "Contacted" and "Negotiating", so I want to move them into the appropriate sheet based on the Communciation status.

The code I have so far is as follows:


	VB:
	
 
Sub UpdateProspects_2() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowContacted As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "2 - Interested" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"K").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"P").Offset(1, 0) 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(7, 1), Cells(7, 45)).Copy 
    Range(Cells(7, 1), Cells(LRowContacted, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 
 
 
Sub UpdateProspects_3() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowNegotiating As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "3 - Negotiating Deal" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 4)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 5), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"X").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"R").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"AB").Offset(1, 0) 
     
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(52, 1), Cells(52, 45)).Copy 
    Range(Cells(52, 1), Cells(LRowNegotiating, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My first attempt was to filter the data, copy the visible range, then paste in new tab. However, it works the first time, but usually not the second time. I dont know if its just the volatility associated with this method, in which case, maybe I'd have to sort/copy/paste?

Any help would be greatly appreciated!

Hi there

I am trying to carry out a relatively simply copy/paste type function between two sheets based on the following criteria:
- range to be copied is from sheet 1A, column G, used range only (from row 6 onwards)
- data is to be "pasted" onto sheet HA2, same row numbers as the data on sheet 1A. The destination column is based on looking up and finding the value "1" in row 6 between columns 9 and 213.

I've tried the following code, using a function defined below, but it doesn't seem to work...

For the lookup of the destination column, I've just tried to find the last (only) column with data....

Please bear in mind, I'm a VBA absolute beginner, and the code I've hatched together is from other code a friend has prepared for me!

Thanks in advance

Sub PostProgressWBS01()
 
Dim cel As Range
Dim rng As Range
'Dim PutCol As Long
Dim PutCol As Long
Dim LastRow As Long
    
    'Get column where data is to be copied to on Sheet HA2
    Set rng = Sheets("HA2").Range(Cells(6, 9).Address, Cells(6, 213).Address)
    PutCol = Find_LastCol_Data(rng)
    
    'Get last Row with DATA on Sheet 1A
    Set rng = Sheets("1A").Range(Cells(18, 7).Address, Cells(9999, 7).Address)
    LastRow = Find_LastRow_Data(rng)
     
    'do the thing
    For Each cel In rng
        If cel.Value <> "" Then
        Sheets("HA2").Cells(cel.Row, PutCol) = cel
        End If
    Next cel

    Set cel = Nothing
    Set rng = Nothing
    
    Application.CutCopyMode = False
End Sub


Function Find_LastCol_Data(rng As Range)

    ' Find the last column
    On Error Resume Next
    Find_LastCol_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    On Error GoTo 0
    
End Function

Function Find_LastRow_Data(rng As Range)

    ' Find the last row
    On Error Resume Next
    Find_LastRow_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
    On Error GoTo 0
    
End Function


I'm running a loop that takes the values of certain cells from a range of rows the user has selected on one sheet, and copies them to cells in another sheet in the same workbook.

It works fine, except it is unbelievably slow... nearly 4 seconds per loop.

Obviously there is something terribly inefficient about my code, but I can't imagine what it is. Can someone suggest a more efficient way to accomplish this? The slow part is lines 3 and 4 of the 5 lines of code below. Each of those 2 lines are taking nearly 2 seconds to process, each time through.

The code takes the values from the 4th and 6th columns in the selected rows and places them in Columns A and B, respectively, starting in row 2, of a worksheet named "Cstates", in the same workbook. It needs to work regardless of the number of rows selected, which sometimes may be up to 1000 rows or so, which would take over an hour.

x = Selection.Rows.Count
For RowCount = 1 To x
        Worksheets("Cstates").Range("A" & RowCount + 1).Value =  Range(Cells(Selection.row + RowCount
- 1, 4).Address).Value
        Worksheets("Cstates").Range("B" & RowCount + 1).Value = Range(Cells(Selection.row + RowCount
- 1, 6).Address).Value
Next RowCount
Help appreciated!

Sorry if the title sounds confusing or misleading but I didn't have a simpler way to put it.

Anyway, down the problem. I am using the code below to copy rows from sheet 'Summary' to sheet 'Final'. The row is copied if the value in column J of that row is not equal to 0.

Sub Copy()
    Dim cl As Range
    Dim ws As Worksheet
    Dim sht As String
    
    sht = "Summary"

Set ws = Worksheets(sht)
    With ws
        For Each cl In .Range("j5:j" & .Range("a65536").End(xlUp).Row)
            If cl.Value <> 0 Then
                cl.EntireRow.Copy Worksheets("Final").Range("A65536").End(xlUp).Offset(1, 0)
            End If
        Next cl
    End With
    
End Sub

However since the values in 'Summary' are dependant on values from other sheets, when I copy these values to 'Final' the links aren't carried over and they appear as "#Values".

How can I modify this code such that it copies only the values, like a Paste Special-Values function?

If needed I can post the worksheet.

Any help is appreciated.

Thanks

Dear Excel Experts,

I'm working on programing a button in excel to copy value from sheet to another one
and to update a named range of cells in the same time

Kindly find the attached excel for more details

Thanks for any help & Kind regards,
SandraDistribution Project1.xlsm

Please Help

I have a spreadsheet with multiple pages and would like a macro to assign to a command button.
The macro needs to loop through each worksheet and extract the value of 1 cell from each worksheet (the same cell in each sheet) and place it into a cell in a predefined worksheet, being offset for each worksheet to create a column of values.

I seem to only be able to extract the formula of the cell but not the value

Thanks for listening.

Viewing sheet "26", I need to take each individual numerical value in column "V", find the matching numerical value in column "J" of sheet "export", and return the cell location.

Using the returned location (cell reference only, the numerical value is unimportant), I need to insert four other data values from sheet "26" into four empty columns in sheet "export", along the same row as that of the referenced cell.

I need to do this for each value in column “V” of sheet “26”. Can anybody write a sample for this code?

I would upload the spreadsheet that i am working with, but i do not know how to trim its size to meet forum guidelines. It is currently 656KB.

I want to copy a cell value from SOURCE sheet to a cell in column X of TARGET sheet in this way

- Look for last cell of Column A of TARGET (example: I have A10 is the last cell).

- Then I will copy to X11 (As the X column may contain blank)

- Actually I have 25 cells like this, but if I have macro for every single cell that will take a lot of time/memory. I think it is possible to make it in one macro, this is the "rule": I will copy in order from B2:B26 (SOURCE sheet), the first cell will be copied to TARGET sheet, cell C11 (Last cell in Col A is A10), and the next cell will be in the every next 3 cells: F11, I11, L11, O11....

Can anyone help me on this.

Thank you so so much for your help.

Ok, I posted this in the 'New User' subforum, but am not having much luck there.

I am not that great with Excel, I know the simple stuff, but I have been asked to come up with a formula for a large spreadsheet which lists telephone calls and their call setup durations.

I have to explain this in depth because it is quite a complex spreadsheet

The calls go through two different telecoms equipment at the same time, and the spreadsheet lists the details of this (duration, time of call, number, etc, etc, etc).

There are two sheets, the first sheet gets info from the second one, and this is used to compare the call duration in seconds.

Most of the call records in the second sheet match up to the corresponding call in the first sheet, (when i say match up i mean they are within 2 seconds difference). However there are a few call records which do not match up. this can be verified by looking at the duration of the call, and the dialled number, together with the time of call.

I have attached up a small part of the spreadsheet with colour coded examples of where and how the calls do not match up.

I need to create a formula to correctly add the values in sheet 2 to to the rows in sheet 1.

I have come up with what I think is the most effective logic to create this formula, all I need is someone to help convert this logic into the cold hard formula

Im going to assume if you are still reading then you will have a look at the spreadsheet I attached, so I will explain it as if you are looking at it.

Formula logic:

Asterisk (sheet 1), G2 = cell value of Squire (sheet 2) column D, on row (x) where the value of Asterisk K2 = value in Squire column H

AND WHERE

Asterisk F2 is = or within +/- 2 of row (x) value in Squire column D

// this is done because there can sometimes be rows in squire column h with the same value, to narrow it down to the correct row we use this AND statement

Then once the correct value for Asterisk G2 is found, the values of Asterisk B2 and L2 can be taken from the same row where the result was found for Asterisk G2 on Squire Column A and H respectively.

Someone.... anyone... please...

thanks!

Hi!
I am desperate to find a solution for my Workbook, for which I need to create a macro. My problem is that i don't really have much experience with macros. I really hope you can help me! That's the situation:

I am creating a summary sheet for a document with over 100 sheets with the following characteristics:
- Not all the sheets are activated
- All the sheets have different names
- All the sheets are structured in the same way

I need a macro to copy the values of a cell range from all the activated sheets and paste them in the summary sheet. It should be able to:

- Copy values of I9:N9 from all the sheets and paste them in...
D5:I5(values of sheet 1)
D6:I6(values of sheet 2)
D7:I7 (values of sheet 3)
D8:I8 (values of sheet 4)
D9:I9 (values of sheet 5)... and so on

- Copy sheet name of activated sheets and paste them on a list on summary sheet starting at C5

The summary sheet already exits and it's called "RESUMEN" (I already have some other information and formulas running on "RESUMEN"). I am interested in ...
...Copying only cells values because they are actually formulas
...Copying only from activated sheets, since I have some inactive sheets that I use as drafts for other macros

I would be really glad if someone could help me! Thank you so much!!!!!!!!!

I have a document.
This consists of say 12 worksheets. The last worksheet has a table. The
values in this table are derived from formulas/references to the other 11
sheets.
I know that if I wanted to copy values from sheet 12 to a NEW document, it
is easy. Just copy, paste special, values etc.
To get more tricky. However, lets say i need to email this document to a
friend, but I want to delete sheets 1-11 before he gets it. IS it possible
to ensure that cell VALUES are shown on sheet 12, before I delete
corresponding sheets 1-11.
I often want to forward sheets to people, and save it as a different name
before adjusting, but then dont want to go to all the hassle of copying and
pasting.

I have excel files with approx 80-220 sheets each.

I have used a "count" formula at the L200 on every sheets.

I want to display the count value at the first sheet against all the sheets.Like if sheet 1 have value 10 in cell l200 then the sheet one column C2 will show 10 , If sheet 2 /cell l200 has 15 value then sheet 1 cell C3 will show 15 ....and it will continue automatically.

Please help Its urgent.
Thanks in advance,
Fatalcore.

Is it possible to copy values from one sheet and then try and find these in another?

For example:

Copied Values From Sheet 1

A
B
D

Find in Sheet 2:

A
B
D

i dont know if these can be done through VBA or not, if somebody could please help?

Thanks in advance.

I have two sheets. Sheet-2 has additional information but Sheet-1 has the dates I need.

See attached sample of what I'm working with. I need to populate Sheet-2!C2 with the Date from Sheet-1 where the ID matches. Do I use VLOOKUP for this or somethign else?

Thanks!

--------------------------------------------------------------------------------

I have excel files with approx 80-220 sheets each.

I have used a "count" formula at the L200 on every sheets.

I want to display the count value at the first sheet against all the sheets.Like if sheet 1 have value 10 in cell l200 then the sheet one column C2 will show 10 , If sheet 2 /cell l200 has 15 value then sheet 1 cell C3 will show 15 ....and it will continue automatically.

Please help Its urgent.
Thanks in advance,
Fatalcore.

Sorry I posted the post in General section.

Hello guys, I have a question here

I have a data and I have 6 worksheets in one workbook.
I would like to compare value between the worksheets at the same column..
for example i would like to compare value of column b row 1 for each worksheets and the maximum value will be copy and pasted into new worksheets..moreover, i also would like to know which sheets the max value is located...

I have attached a sample here..
May u all help me..Thanks a lot..

Regards..
Book1.xls

Hi all,

i have found this code on ozgrid to search between a min max value, trouble is it only selects the first cell it finds. How can it be modifide to copy each value in between the min max set say to sheet 2?

Here is the code


	VB:
	
 
 
Private Sub CommandButton1_Click() 
     
    Dim strNum As String 
    Dim lMin As Long, lMax As Long 
    Dim rFound As Range, rLookin As Range 
    Dim lFound As Long, rStart As Range 
    Dim rCcells As Range, rFcells As Range 
    Dim lCellCount As Long, lcount As Long 
    Dim bNoFind As Boolean 
     
    strNum = InputBox("Please enter the lowest value, then a comma, " _ 
    & "followed by the highest value" & vbNewLine & _ 
    vbNewLine & "E.g. 1,10", "GET BETWEEN") 
     
    If strNum = vbNullString Then Exit Sub 
    On Error Resume Next 
    lMin = Left(strNum, InStr(1, strNum, ",")) 
    If Not IsNumeric(lMin) Or lMin = 0 Then 
        MsgBox "Error in your entering of numbers, or Min was a zero", vbCritical, "Ozgrid.com" 
        Exit Sub 
    End If 
     
    lMax = Replace(strNum, lMin & ",", "") 
    If Not IsNumeric(lMax) Or lMax = 0 Then 
        MsgBox "Error in your entering of numbers, or Max was a zero", vbCritical, "Ozgrid.com" 
        Exit Sub 
    End If 
     
    If lMax < lMin Then 
        MsgBox "Min is greater than Max", vbCritical, "Ozgrid.com" 
        Exit Sub 
    End If 
     
     
    If lMin + 1 = lMax Then 
        MsgBox "No scope between Min and Max", vbCritical, "Ozgrid.com" 
        Exit Sub 
    End If 
     
     
    If Selection.Cells.Count = 1 Then 
        Set rCcells = Cells.SpecialCells(xlCellTypeConstants, xlNumbers) 
        Set rFcells = Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) 
        Set rStart = Cells(1, 1) 
    Else 
        Set rCcells = Selection.SpecialCells(xlCellTypeConstants, xlNumbers) 
        Set rFcells = Selection.SpecialCells(xlCellTypeFormulas, xlNumbers) 
        Set rStart = Selection.Cells(1, 1) 
    End If 
     
     'Reduce down range to look in
    If rCcells Is Nothing And rFcells Is Nothing Then 
        MsgBox "You Worksheet contains no numbers", vbCritical, "ozgrid.com" 
        Exit Sub 
    ElseIf rCcells Is Nothing Then 
        Set rLookin = rFcells.Cells 'formulas
    ElseIf rFcells Is Nothing Then 
        Set rLookin = rCcells.Cells 'constants
    Else 
        Set rLookin = Application.Union(rFcells, rCcells) 'Both
    End If 
     
    lCellCount = rLookin.Cells.Count 
    Do Until lFound > lMin And lFound < lMax And lFound > 0 
        lFound = 0 
        Set rStart = rLookin.Cells.Find(What:="*", After:=rStart, LookIn:=xlValues, _ 
        LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
        SearchDirection:=xlNext, MatchCase:=True) 
        lFound = rStart.Value 
        lcount = lcount + 1 
        If lCellCount = lcount Then 
            bNoFind = True 
            Exit Do 
        End If 
    Loop 
     
    rStart.Select 
     
    If bNoFind = True Then 
        MsgBox "No numbers between " _ 
        & lMin & " and " & lMax, vbInformation, "Ozgrid.com" 
    End If 
    On Error Goto 0 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I would like to copy all formulas from one sheet to another without overwriting the existing values in cells where no formulas exist.

I have tried selecting all cells from a sheet....copying then going to another sheet and paste special Formulas.

This copy's the formulas but also over-writes the cells with data only in them.

Hope this makes sense...