Rectangle 27 1

Instead of the many lines of copy/paste with specific ranges, use a loop, Reducing 25-50 lines into 3.

To be used in your specific example, because you happen to be transposing rows to columns. The row number happens to match the column number you are pasting into, so embrace it with a loop.

Private Sub CopyAuditData_Click()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim DestRow As Long, lRow As Long

    Set ws1 = Sheets("Call Audit Sheet")
    Set ws2 = Sheets("HiddenData")
    DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1

    'CHANGES HERE
    For lRow = 1 To 25
        ws2.Cells(DestRow, lRow).Value = ws1.Cells(lRow, "V").Value 
    Next lRow

End Sub

Where lRow = 1 Using it as both the row of the source and column of target.

ws2.Cells(DestRow, 1)
ws2.Range("A" & DestRow)
ws1.Cells(lRow, "V")
ws1.Range("V1")

excel - Copy cell range from one worksheet and paste in a different wo...

excel excel-vba
Rectangle 27 1

  • Copy the row which has cell value > 255 characters to the beginning of the excel, make that row the first row in the excel
  • change the registry value from the above link.

sql server 2008 - Text was truncated or one or more characters had no ...

sql-server-2008 excel ssis codepages import-from-excel
Rectangle 27 2

This macro will do what you want but instead of inserting rows it will put the data into a new sheet; You just need to add a sheet for output and change the name for input and output sheets in the code.

Dim mOut As Worksheet
Dim mInp As Worksheet
Dim num As Integer
Dim i As Integer
Dim j As Integer
Dim c As Integer

Sub Copy()

Set mInp = Worksheets("Your Sheet Name")
Set mOut = Worksheets("Create Another Sheet for Output")

mOut.Cells(1, 1) = mInp.Cells(1, 1)
mOut.Cells(1, 2) = mInp.Cells(1, 2)
mOut.Cells(1, 3) = mInp.Cells(1, 3)
mOut.Cells(1, 4) = "Req"

i = 2
num = 2

While mInp.Cells(i, 1) <> ""
c = mInp.Cells(i, 3)

For j = 1 To c

mOut.Cells(num, 1) = mInp.Cells(i, 1)
mOut.Cells(num, 2) = mInp.Cells(i, 2)
mOut.Cells(num, 3) = mInp.Cells(i, 3)
mOut.Cells(num, 4) = mInp.Cells(i, j + 3)

num = num + 1
Next j

i = i + 1
Wend

End Sub

If you want to pursue the solution that you had by inserting the rows you need to add this loop after you inserted them. Also, you need to count number of rows while adding them. I do not have your code to see how it can be done but am sure it is easy to do so.

For i = 2 To NumRows 'Number of rows (Sum of the inserted and original rows)
         If mInp.Cells(i, 1) <> "" Then

             irow = i
             Count = 1

         Else

             mInp.Cells(i, 1) = mInp.Cells(irow, 1)
             mInp.Cells(i, 2) = mInp.Cells(irow, 2)
             mInp.Cells(i, 3) = mInp.Cells(irow, 3)
             mInp.Cells(i, 4) = mInp.Cells(irow, 4 + Count)

             Count = Count + 1

         End If
 Next i

Excel VBA to insert rows based on cell value and copy columns down bas...

excel vba excel-vba
Rectangle 27 1

you could work with arrays and slice them with Application.Index()

Sub main()
    Dim data1 As Variant, data2 As Variant
    Dim i As Long

    With Range("A2", Cells(Rows.Count, "A").End(xlUp))
        data1 = .Resize(, 3).Value
        data2 = .Offset(, 3).Resize(, 25).Value
        .Resize(, 28).ClearContents
    End With
    For i = LBound(data1) To UBound(data1)
        With Cells(Rows.Count, 1).End(xlUp).Offset(1)
            .Resize(data1(i, 3), 3) = Application.Index(data1, i, 0)
            .Offset(, 3).Resize(data1(i, 3), 1) = Application.Transpose(Application.Index(data2, i, 0))
        End With
    Next
End Sub

@AmyWilliford, out of curioisty: what made you choose Masoud solution?

When I first got in the office that morning, his was the only answer at that point. I gave it a try and it worked. I apologize, I didn't see your response until after I had it working. I will definitely try your suggestion today.

Excel VBA to insert rows based on cell value and copy columns down bas...

excel vba excel-vba
Rectangle 27 0

Sub Parse_Furniture_Suppliers()
    Dim tmpCell As Range, rngHeaders As Range, rngTarget As Range

    Set rngHeaders = ActiveSheet.Range("A1:F1")
    Set tmpCell = ActiveSheet.Range("C2")

    Workbooks.Add
    ActiveSheet.Range("A1:F1").Value = rngHeaders.Value
    Set rngTarget = ActiveSheet.Range("A2")
    rngTarget.Select
    ActiveWindow.FreezePanes = True
    rngTarget.Resize(1, 6).Value = tmpCell.Offset(0, -2).Resize(1, 6).Value
    Set rngTarget = rngTarget.Offset(1)
    Set tmpCell = tmpCell.Offset(1)

    Do While tmpCell.Value <> ""
        If tmpCell.Value <> tmpCell.Offset(-1).Value Then
            ActiveWorkbook.SaveAs tmpCell.Offset(-1).Value
            ActiveWorkbook.Close
            Workbooks.Add
            ActiveSheet.Range("A1:F1").Value = rngHeaders.Value
            Set rngTarget = ActiveSheet.Range("A2")
            rngTarget.Select
            ActiveWindow.FreezePanes = True
        End If

        rngTarget.Resize(1, 6).Value = tmpCell.Offset(0, -2).Resize(1, 6).Value
        Set rngTarget = rngTarget.Offset(1)
        Set tmpCell = tmpCell.Offset(1)
    Loop

    ActiveWorkbook.SaveAs tmpCell.Offset(-1).Value
    ActiveWorkbook.Close
End Sub

vba - Excel Macro to copy all cells containing current array value and...

excel vba excel-vba copy-paste
Rectangle 27 0

Sub CondCopy()

    Dim ws0 As Worksheet, ws1 As Worksheet
    Dim str0 As String, str1 As String, str2 As String
    Dim strCond As String, ArrLoc As Long
    Dim strCopy As String, strPaste As String, strNum As String

    With ThisWorkbook
        Set ws0 = .Sheets("Sheet1")
        Set ws1 = .Sheets("Sheet2")
    End With

    str0 = ";One;Two;Three;Four;Five;Six;Seven;Eight;Nine;Ten;Eleven"
    str1 = ";Eleven;Ten;Nine;Eight;Seven;Six;Five;Four;Three;Two;One"
    str2 = "BUD;F01;F02;F03;F04;F05;F06;F07;F08;F09;F10;F11"
    strCond = ws0.Range("D6").Value

    ArrLoc = Application.Match(strCond, Split(str2, ";"), 0) - 1
    strNum = Split(str0, ";")(ArrLoc) & Split(str1, ";")(ArrLoc)

    strCopy = "CopyFormulasFT" & strNum
    strPaste = "PasteFormulasFT" & strNum

    With ws1
        .Range(strCopy).Copy
        .Range(strPaste).PasteSpecial xlPasteValues, SkipBlanks:=True
    End With

End Sub

In the case that you need to add more named ranges following your pattern, just editing str0, str1, and str2 is enough.

This above code is great, however when it pastes it pastes inverted, should only paste the columns to the right of the range, it's copying appropriately but not pasting appropriately within the range. Looking to see if I can figure it out.

Ah figured it out, was missing the strNum when I copied the above code, thank-you!

Kindly mark the answer as accepted if it helped you. This is how we say thanks in SO. :)

Question, I had another piece of code I was trying to manipulate using your above code however I keep getting the "Range of object worksheet failed" error.

Thanks for accepting. :) Kindly start a new question if it's a different code or problem from this one. :)

excel - Copy Range From One Sheet Paste Part of Range In Same Sheet Ba...

excel vba excel-vba copy-paste named-ranges
Rectangle 27 0

Use WorksheetFunction.Transpose(Array) method to print an array to spreadsheet. It's an efficient (and built-in) method widely used to print an array to a spreadsheet in one go.

Avoid comments like End if 'end of end if as anybody reading your code will know that already. More about the DRY principle.

The downside of VBA Arrays is that you always have to specify the size at the creation time. It's a long topic and there are alternative ways, avoiding arrays etc, but I am not going to discuss it here. A workaround is to start at 0 and then resize(increase) the array as you go using ReDim Preserve

now a short version of your code would be

Public Sub loopingTest()
    Dim i As Long: ReDim ArrayTest(0)
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Range("B" & i) = 4 Then
            ArrayTest(UBound(ArrayTest)) = Range("A" & i)
            ReDim Preserve ArrayTest(UBound(ArrayTest) + 1)
        End If
    Next i
    Range("D1:D" & UBound(ArrayTest)) = WorksheetFunction.Transpose(ArrayTest)
End Sub

You can use a variable instead of 4

Public Sub loopingTest()

    Dim lastRow As Long
    Dim myNumber as Long
    myNumber = 5
    Dim i As Long
    ReDim ArrayTest(0)

    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ' will get the last row

    For i = 1 To lastRow

        If Range("B" & i) = myNumber Then 

            ArrayTest(UBound(ArrayTest)) = Range("A" & i) 
            ReDim Preserve ArrayTest(UBound(ArrayTest) + 1)

        End If

    Next i

    Range("D1:D" & UBound(ArrayTest)) = WorksheetFunction.Transpose(ArrayTest)

End Sub

Thanks a lot. It seems to be working, but is there a way to the array to automatically update when I manually change numbers on column B?

@JacobRuvalcaba the way to update the array would be to rerun a macro. See my updated answer. Here, on stackoverflow we say thank you by accepting and upvoting answers :)

actually if i update the values in column B my previous results in column D are still showing up. So How do i clear an array before showing the array again?

Range("D:D").ClearContents
Dim lastRow As Long

excel - Looping through a column and copying values from cell into an ...

arrays excel vba excel-vba
Rectangle 27 0

I gave it another approach aside from SQL and sorts (already provided before). I tested this code and it works.

The general idea behind this code:

  • Class module "clsSheet" contains all information per sheet, ie. column headers A, B, C, but also the used range, the array in which this range is loaded and the maximum row / col.
  • These self created data objects are loaded into a collection, after which the next part of the code will execute all code in memory (fast).
  • A dictionary is created and will contain the "module name" (ie. module1,2,3 etc...) as key, and a clsModule object as value. When a key (thus module name) does not exist yet, a new item will be added.
  • The clsModule class keeps the information on each modulename, ie. columns A, B and C information. The information is stored in the form of arrays.
  • When all information is stored in the dictionary, it is only a matter of translating the dictionary content back to a form that is preferred. In this case, I chose to give to each sheet the name of dictionary keys and load the data to their corresponding sheets.
  • Dynamically finds headers with names "A", "B" and "C", which reduces risk for bugs;
  • Creates a new workbook and writes the values for each "module" to a different sheet.
  • These classes are re-usable in other situations with minimum modification needed.

The major benefit of this approach is flexibility. Since you load all data in a framework, you can virtually perform any actions afterwards by setting the classes and calling their properties.

Sub GetModules()


Dim cSheet                      As clsSheet
Dim cModule                     As clsModule
Dim oSheet                      As Excel.Worksheet
Dim oColl_Sheets                As Collection
Dim oDict                       As Object
Dim vTemp_Array_A               As Variant
Dim vTemp_Array_B               As Variant
Dim vTemp_Array_C               As Variant

Dim lCol_A                      As Long
Dim lCol_B                      As Long
Dim lCol_C                      As Long
Dim lMax_Row                    As Long
Dim lMax_Col                    As Long
Dim oRange                      As Range
Dim oRange_A                    As Range
Dim oRange_B                    As Range
Dim oRange_C                    As Range
Dim vArray                      As Variant

Dim lCnt                        As Long
Dim lCnt_Modules                As Long

Dim oBook                       As Excel.Workbook
Dim oSheet_Results              As Excel.Worksheet


Set oColl_Sheets = New Collection
Set oDict = CreateObject("Scripting.Dictionary")

'Get number of columns, rows and headers A, B, C dynamically
'This is useful in case columns are inserted
For Each oSheet In ThisWorkbook.Sheets

    Set cSheet = New clsSheet
    Set cSheet = cSheet.get_Sheet_Data(cSheet, oSheet)

    oColl_Sheets.Add cSheet

Next oSheet

'At this point, your entire sheet data structure is already contained in the collection oColl_Sheets

Set cSheet = Nothing

'Loop through the sheet objects and retrieve the values into modules
For Each cSheet In oColl_Sheets

    'Now you load back all data from the sheet and perform loops in memory through the arrays
    lCol_A = cSheet.fA_Col
    lCol_B = cSheet.fB_Col
    lCol_C = cSheet.fC_Col
    lMax_Row = cSheet.fMax_Row
    lMax_Col = cSheet.fMax_Col
    Set oRange = cSheet.fRange
    vArray = cSheet.fArray

    For lCnt = 1 To lMax_Row - 1

        'Check if the module already exists
        If Not oDict.Exists(vArray(1 + lCnt, 1)) Then  '+1 due to header
            lCnt_Modules = lCnt_Modules + 1
            Set cModule = New clsModule

            'Add to dictionary when new module (thus key) is new
            Set cModule = cModule.Add_To_Array_A(cModule, lCol_A, vArray(1 + lCnt, lCol_A), True)
            Set cModule = cModule.Add_To_Array_B(cModule, lCol_B, vArray(1 + lCnt, lCol_B), True)
            Set cModule = cModule.Add_To_Array_C(cModule, lCol_C, vArray(1 + lCnt, lCol_C), True)

            oDict.Add vArray(1 + lCnt, 1), cModule

        Else

            Set cModule = oDict(vArray(1 + lCnt, 1))

            'Replace when module (thus key) already exists
            Set cModule = cModule.Add_To_Array_A(cModule, lCol_A, vArray(1 + lCnt, lCol_A), False)
            Set cModule = cModule.Add_To_Array_B(cModule, lCol_A, vArray(1 + lCnt, lCol_B), False)
            Set cModule = cModule.Add_To_Array_C(cModule, lCol_A, vArray(1 + lCnt, lCol_C), False)

            Set oDict(vArray(1 + lCnt, 1)) = cModule

        End If

    Next lCnt

Next cSheet

'Now you have all the data available in your dictionary: per module (key), there is an array with the data you need.
'The only thing you have to do is open a new workbook and paste the data there.
'Below an example how you can paste the results per worksheet

Set oBook = Workbooks.Add
Set oSheet_Results = oBook.Sheets(1)

lCnt = 0
For lCnt = 0 To oDict.Count - 1

    'Fill in values from dictionary
    oBook.Sheets.Add().Name = oDict.Keys()(lCnt)
    ReDim vTemp_Array_A(1 To UBound(oDict.Items()(lCnt).fA_Arr))
    ReDim vTemp_Array_B(1 To UBound(oDict.Items()(lCnt).fB_Arr))
    ReDim vTemp_Array_C(1 To UBound(oDict.Items()(lCnt).fC_Arr))
    oBook.Sheets(oDict.Keys()(lCnt)).Range("A1").Value = "A"
    oBook.Sheets(oDict.Keys()(lCnt)).Range("B1").Value = "B"
    oBook.Sheets(oDict.Keys()(lCnt)).Range("C1").Value = "C"

    vTemp_Array_A = oDict.Items()(lCnt).fA_Arr
    vTemp_Array_B = oDict.Items()(lCnt).fB_Arr
    vTemp_Array_C = oDict.Items()(lCnt).fC_Arr
    Set oRange_A = oBook.Sheets(oDict.Keys()(lCnt)).Range(Cells(2, 1), Cells(1 + UBound(vTemp_Array_A), 1))
    Set oRange_B = oBook.Sheets(oDict.Keys()(lCnt)).Range(Cells(2, 2), Cells(1 + UBound(vTemp_Array_B), 2))
    Set oRange_C = oBook.Sheets(oDict.Keys()(lCnt)).Range(Cells(2, 3), Cells(1 + UBound(vTemp_Array_C), 3))
    oRange_A = Application.Transpose(vTemp_Array_A)
    oRange_B = Application.Transpose(vTemp_Array_B)
    oRange_C = Application.Transpose(vTemp_Array_C)

Next lCnt

Set oColl_Sheets = Nothing
Set oRange = Nothing
Set oDict = Nothing

End Sub
Option Explicit

Private pModule_Nr              As Long
Private pA_Arr                  As Variant
Private pB_Arr                  As Variant
Private pC_Arr                  As Variant

Public Function Add_To_Array_A(cModule As clsModule, lCol As Long, vValue As Variant, bNew As Boolean) As clsModule

Dim vArray As Variant

vArray = cModule.fA_Arr

If bNew Then
    ReDim vArray(1 To 1)
    vArray(1) = vValue
Else
    ReDim Preserve vArray(1 To UBound(vArray) + 1)
    vArray(UBound(vArray)) = vValue
End If

cModule.fA_Arr = vArray

Set Add_To_Array_A = cModule

End Function

Public Function Add_To_Array_B(cModule As clsModule, lCol As Long, vValue As Variant, bNew As Boolean) As clsModule

Dim vArray As Variant

vArray = cModule.fB_Arr

If bNew Then
    ReDim vArray(1 To 1)
    vArray(1) = vValue
Else
    ReDim Preserve vArray(1 To UBound(vArray) + 1)
    vArray(UBound(vArray)) = vValue
End If

cModule.fB_Arr = vArray

Set Add_To_Array_B = cModule

End Function

Public Function Add_To_Array_C(cModule As clsModule, lCol As Long, vValue As Variant, bNew As Boolean) As clsModule

Dim vArray As Variant

vArray = cModule.fC_Arr

If bNew Then
    ReDim vArray(1 To 1)
    vArray(1) = vValue
Else
    ReDim Preserve vArray(1 To UBound(vArray) + 1)
    vArray(UBound(vArray)) = vValue
End If

cModule.fC_Arr = vArray

Set Add_To_Array_C = cModule

End Function


Property Get fModule_Nr() As Long
    fModule_Nr = pModule_Nr
End Property

Property Let fModule_Nr(lModule_Nr As Long)
    pModule_Nr = lModule_Nr
End Property

Property Get fA_Arr() As Variant
    fA_Arr = pA_Arr
End Property

Property Let fA_Arr(vA_Arr As Variant)
    pA_Arr = vA_Arr
End Property

Property Get fB_Arr() As Variant
    fB_Arr = pB_Arr
End Property

Property Let fB_Arr(vB_Arr As Variant)
    pB_Arr = vB_Arr
End Property

Property Get fC_Arr() As Variant
    fC_Arr = pC_Arr
End Property

Property Let fC_Arr(vC_Arr As Variant)
    pC_Arr = vC_Arr
End Property
Option Explicit
Private pMax_Col                As Long
Private pMax_Row                As Long
Private pArray                  As Variant
Private pRange                  As Range
Private pA_Col                  As Long
Private pB_Col                  As Long
Private pC_Col                  As Long

Public Function get_Sheet_Data(cSheet As clsSheet, oSheet As Excel.Worksheet) As clsSheet

Dim oUsed_Range         As Range
Dim lLast_Col           As Long
Dim lLast_Row           As Long
Dim iCnt                As Integer
Dim vArray              As Variant
Dim lNr_Rows            As Long
Dim lNr_Cols            As Long

Dim lCnt                As Long


With oSheet
    lLast_Row = .Cells(.Rows.Count, "A").End(xlUp).Row
    lLast_Col = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

oSheet.Activate
Set oUsed_Range = oSheet.Range(Cells(1, 1), Cells(lLast_Row, lLast_Col))
cSheet.fRange = oUsed_Range
lNr_Rows = oUsed_Range.Rows.Count
cSheet.fMax_Row = lNr_Rows
lNr_Cols = oUsed_Range.Columns.Count
cSheet.fMax_Col = lNr_Cols
ReDim vArray(1 To lNr_Rows, 1 To lNr_Cols)
vArray = oUsed_Range
cSheet.fArray = vArray

For lCnt = 1 To lNr_Cols
    Select Case vArray(1, lCnt)

        Case "A"
            cSheet.fA_Col = lCnt
        Case "B"
            cSheet.fB_Col = lCnt
        Case "C"
            cSheet.fC_Col = lCnt

    End Select
Next lCnt

Set get_Sheet_Data = cSheet

End Function

Property Get fMax_Col() As Long
    fMax_Col = pMax_Col
End Property

Property Let fMax_Col(lMax_Col As Long)
    pMax_Col = lMax_Col
End Property

Property Get fMax_Row() As Long
    fMax_Row = pMax_Row
End Property

Property Let fMax_Row(lMax_Row As Long)
    pMax_Row = lMax_Row
End Property

Property Get fRange() As Range
    Set fRange = pRange
End Property

Property Let fRange(oRange As Range)
    Set pRange = oRange
End Property

Property Get fArray() As Variant
    fArray = pArray
End Property

Property Let fArray(vArray As Variant)
    pArray = vArray
End Property

Property Get fA_Col() As Long
    fA_Col = pA_Col
End Property

Property Let fA_Col(lA_Col As Long)
    pA_Col = lA_Col
End Property

Property Get fB_Col() As Long
    fB_Col = pB_Col
End Property

Property Let fB_Col(lB_Col As Long)
    pB_Col = lB_Col
End Property

Property Get fC_Col() As Long
    fC_Col = pC_Col
End Property

Property Let fC_Col(lC_Col As Long)
    pC_Col = lC_Col
End Property

vba - Copy specific rows from excel based on a specific cell value - S...

excel vba excel-vba vbscript
Rectangle 27 0

With sheets("sheetname").range("I10") 
          .PasteSpecial Paste:=xlPasteValues, _
                  Operation:=xlNone, _
                  SkipBlanks:=False, _
                  Transpose:=False
          .PasteSpecial Paste:=xlPasteFormats, _
                  Operation:=xlNone, _
                  SkipBlanks:=False, _
                  Transpose:=False
          .font.color = sheets("sheetname").range("F10").font.color
      End With
      sheets("sheetname").range("I10:J10").merge

Avoding select is a better approach. But, it seems that this method also don't capture the font colors as in the source cell (cell is having text with different font colors)

I updated the answer that should include font copying. Hope this is what you're looking for

Copying the cell value preserving the formatting from one cell to anot...

excel vba formatting copy cell
Rectangle 27 0

First, I suggest that you use the Record Macro facility on the Developer ribbon. Be sure to set "Use Relative References" on, and record the mouse clicks and keystrokes of one iteration of what you want to do (copy A1 to B1). Then open the macro in VB and modify it.

Sub Macro1()

Dim NumRows As Integer
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Activate

    For i = 1 To NumRows

        ActiveCell.Select
        Selection.Copy
        ActiveCell.Offset(0, 1).Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        ActiveCell.Offset(1, -1).Activate

    Next

End Sub

VBA | How to Copy value from Cell to Cell in Excel - Stack Overflow

excel vba excel-vba copy-paste
Rectangle 27 0

Sub ImportFromDatabase()

    strSearch1 = Sheets("manual").Range("C11")

    Const fromFile = "otherfile.xlsm"

    Dim srcBook As Workbook
    Set srcBook = Application.Workbooks.Open(fromFile, _
        UpdateLinks:=False, _
        ReadOnly:=True, _
        AddToMRU:=False)

         Application.DisplayAlerts = False

    With wrkbk
        Set Value1 = srcBook.Sheets("DAx_data").Rows(3).Find(What:=strSearch1, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

           srcBook.Sheets("DAx_data").Columns(Value1.Column).Copy
           ThisWorkbook.Sheets("source").Columns(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone


         Application.DisplayAlerts = True
         Application.CutCopyMode = False

    End With
        srcBook.Close False

    End Sub

It's not pretty, and I'm sure that that this can be made better by using a Range but my knowledge of VBA is not that good.

excel - Copy column based on cell value - Stack Overflow

excel vba copy-paste
Rectangle 27 0

I'm guessing you want a formula, and not instructions on copy and paste.

Cell references can be made using the following format.

=[Workbook Name]SheetName!CellAddress

Example, you have two workbooks open, Book1 and Book2.

=[Book2]Sheet1!A1
Hello World

The value of [Book1]Sheet1!A1 will now be "Hello World" (it will always reflect the value that is in [Book2]Sheet1!A1, providing the book is open)

If you do want a copy of the actual value, and not a reference to it, you will need to just use plain copy and paste or use the above formulas and when you're done use copy+paste special (paste values) to convert the formulas to their values.

Just realised it was tagged excel-vba so you'd need to use something like @DavidZemens' answer.

You could also use the macro recorder to do this, but doing it manually like David suggests will result in nicer code that's easier to re-use/modify later on.

How to copy a cell value from one excel file and paste it to another e...

excel-vba
Rectangle 27 0

This is the bones of what you need, you can do a lot more with looping over ranges of cells, sheets in workbooks, etc., but fundamentally you will need to specify in VBA what is the target workbook, worksheet, and cell range, and then set that .Value equal to a specific workbook/worksheet/cell .Value in another workbook.

I use Workbook and Worksheet variables in this example, you can also use range variables for the cell(s) if you desire or if you need to apply to multiple cells.

SUb TransferValues()
Dim wb1 as Workbook
Dim wb2 as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet



Set wb1 = Workbooks("First Workbook.xlsx") '<modify as needed'
Set ws1 - wb1.Sheets("The sheet name") '<modify as needed'

Set wb2 = Workbooks("Another Workbook.xlsx") '<modify as needed'
Set ws2 = wb2.Sheets("Another sheet name") 'modify...'


'This line puts the value from wb1.ws1 in wb2.ws2, cells specified:
ws2.Range("A1").Value = ws1.Range("A1").Value


End Sub

How to copy a cell value from one excel file and paste it to another e...

excel-vba
Rectangle 27 0

Sub MultiRecords()

    Dim b As Workbook
    Dim ws As Worksheet
    Dim c As Range, v, i As Long

    Set b = Workbooks.Open("C:\Users\uspola00\Desktop\Headcount_Final.xlsx")
    Set ws = b.Sheets("Headcount")
    Set c = ws.Cells(Rows.Count, "G").End(xlUp)

    Do
       v = Trim(c.Value)
       If v Like "######" Or v Like "#########" Then
           i = Len(v) / 3
           c.Offset(1, 0).Resize(i - 1).EntireRow.Insert
           c.Resize(i).EntireRow.FillDown
           c.Value = Left(v, 3)
           c.Offset(1, 0).Value = Mid(v, 4, 3)
           If Len(v) = 9 Then c.Offset(2, 0).Value = Right(v, 3)
       End If

       If c.Row = 1 Then Exit Do
       Set c = c.Offset(-1, 0)
    Loop
End Sub

Tim Williams....Thank you....worked like a charm!!

excel - Extract values from cell and copy entire row with each individ...

excel vba excel-vba
Rectangle 27 0

As you just want to copy one cell and it (hopefully) is a number you just could write:

Worksheets(stockcode & "_Stock ratio_" & marketcode).Range("D16") = Worksheets(stockcode & "_CF_" & marketcode).Range("B11")* -1

excel - copy and paste a cell from one worksheet to another and multip...

excel vba excel-vba
Rectangle 27 0

Sub productsTest()

Dim st1, st2 As Worksheet
Set st1 = Sheets("Sheet4")
Set st2 = Sheets("Sheet3")
Dim items As Range
Set items = st1.Range(st1.Range("A1"), st1.Range("A" & Rows.Count).End(xlUp))
Dim item As Range

For Each item In items
    Dim cost As String
    Dim code As String
    Dim R As Long
    Dim C As Long
    item.Activate
    R = ActiveCell.Row
    C = ActiveCell.Column
    cost = item.Offset(0, 1).Value
    code = item.Offset(0, 2).Value
    If cost = "0" Then
        Dim prodPos As Range
        Dim prodColors As Range
        Dim prodColor As Range
        Dim colorcost As String
        Dim color As String

        Set prodPos = st2.Cells.Find(What:=code, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        Set prodColors = Range(prodPos.Offset(1, -1), prodPos.Offset(6, 6))

        'I added a For statement for each color
        For Each prodColor In prodColors
            If prodColor.Value = "CLR" Then
                color = prodColor.Value
                colorcost = prodColor.Offset(0, 1).Value
                st1.Cells(R, C).Offset(0, 2).Value = color
                st1.Cells(R, C).Offset(0, 1).Value = colorcost
            End If
        Next prodColor
        For Each prodColor In prodColors
            If prodColor.Value = "PGX" Then
                color = prodColor.Value
                colorcost = prodColor.Offset(0, 1).Value
                st1.Range("A" & R & ":D" & R).Select
                Selection.Copy
                Selection.Insert Shift:=xlDown
                st1.Cells(R, C).Offset(0, 2).Value = color
                st1.Cells(R, C).Offset(0, 1).Value = colorcost
            End If
        Next prodColor
        For Each prodColor In prodColors
            If prodColor.Value = "TGY" Then
                color = prodColor.Value
                colorcost = prodColor.Offset(0, 1).Value
                st1.Range("A" & R & ":D" & R).Select
                Selection.Copy
                Selection.Insert Shift:=xlDown
                st1.Cells(R, C).Offset(0, 2).Value = color
                st1.Cells(R, C).Offset(0, 1).Value = colorcost
            End If
        Next prodColor
                    For Each prodColor In prodColors
            If prodColor.Value = "TVG" Then
                color = prodColor.Value
                colorcost = prodColor.Offset(0, 1).Value
                st1.Range("A" & R & ":D" & R).Select
                Selection.Copy
                Selection.Insert Shift:=xlDown
                st1.Cells(R, C).Offset(0, 2).Value = color
                st1.Cells(R, C).Offset(0, 1).Value = colorcost
            End If
        Next prodColor
                    For Each prodColor In prodColors
            If prodColor.Value = "GYC" Then
                color = prodColor.Value
                colorcost = prodColor.Offset(0, 1).Value
                st1.Range("A" & R & ":D" & R).Select
                Selection.Copy
                Selection.Insert Shift:=xlDown
                st1.Cells(R, C).Offset(0, 2).Value = color
                st1.Cells(R, C).Offset(0, 1).Value = colorcost
            End If
        Next prodColor
                    For Each prodColor In prodColors
            If prodColor.Value = "PGX" Then
                color = prodColor.Value
                colorcost = prodColor.Offset(0, 1).Value
                st1.Range("A" & R & ":D" & R).Select
                Selection.Copy
                Selection.Insert Shift:=xlDown
                st1.Cells(R, C).Offset(0, 2).Value = color
                st1.Cells(R, C).Offset(0, 1).Value = colorcost
            End If
        Next prodColor
    End If
Next item

End Sub

excel - Find a cell value, look up corresponding code in another sheet...

excel vba excel-vba
Rectangle 27 0

The problem seems to be that your If statement is looking for a folder, not a file. Unless your column of names includes file extensions, you haven't given your If statement enough information to check for a file.

If
"C:\Users\Workbook Folder\Names\Joe Smith"

Since there isn't a file extension, the If statement thinks "Joe Smith" is a folder.

To fix this, you can add a file extension to your code like this:

If Dir(ThisPath & "\" & "Names" & "\" & rCell & ".xls*") = "" Then

Note the use of a wildcard to check for all file extensions that begin with ".xls".

excel - Loop through Workbook to Create New Workbooks and Save them Wi...

excel vba excel-vba
Rectangle 27 0

There is no way to guarantee that you can stop a person with Excel skills from copying your workbook. For example, even if I can't see a value in a cell (because you have locked the viewing area) I can still access it via a formula from a different workbook. Password protection? There are many many websites that will crack passwords in Office documents.

A method that might work, however, is to use create a validation server. You could have your own web service that authenticates user names and passwords against motherboard serial numbers. But, even then, a skilled user could reverse engineer your workbook and remove the code that would call the web service.

Anti-piracy procedure for Excel workbook - Stack Overflow

excel excel-vba
Rectangle 27 0

Following on from the last comments, here's a piece of code I used recently to turn the last ID value of a record set into variable for use in VBA. It's not great, however, because I still can't work out how to turn the record's ID field value directly into a variable. Instead I used the inelegant solution of copying the record set into an excel workbook, and then setting the variable value to the value of the cell I just copied into.

EDIT: Worked out how to turn the ID into a simple variable: new code at end

This is all run from a single client workbook:

Option Explicit
Public AftUpD As Long
Public BfrUpD As Long

Sub AssignLstRowAftUpD2()

    Dim dbPP As DAO.Database
    Dim ResTemp As DAO.Recordset
    Dim z As Long
    Dim SelectLast As String

    SelectLast = "SELECT Max(Table1.ID) AS MaxOfID FROM Table1"
    'Debug.Print SelectLast

    Set dbPP = OpenDatabase("C:\filepath\Database11.mdb")
    Set ResTemp = dbPP.OpenRecordset(SelectLast)

    If ResTemp.EOF Then
            GoTo EndLoop
        End If

    Worksheets("Diagnostics").Visible = True
    Worksheets("Diagnostics").Range("C4").CopyFromRecordset ResTemp
    z = Sheets("Diagnostics").Range("C4").Value
    Sheets("Diagnostics").Visible = False
    AftUpD = z
    'Debug.Print AftUpD

EndLoop:
        ResTemp.Close
        dbPP.Close



    Set dbPP = Nothing
    Set ResTemp = Nothing
    'Set SelectionLast = Nothing
    'z = Nothing

End Sub

Then I used this value as a variable to make a new SQL query:

Sub Query()
   'This query uses the highest ID value in a companion spreadsheet (the public  
   'variable BfrUpD), which is set in a sub I haven't posted here, to find out 
   'how many records have been added to the database since the last time the
   'spreadsheet was updated, and then copies the new records into the workbook

   'Be warned: If you run this query when BfrUpD is equal to or greater than AftUpD it 
   'will cause a crash. In the end user version of this, I use several If tests,
   'comparing BfrUpD with other public variables, to make sure that this doesn't
   'happen.
    Dim WBout As Excel.Workbook, WSout As Excel.Worksheet
    Dim dbPP1 As DAO.Database
    Dim qryPP1 As DAO.Recordset
    Dim ResTemp1 As DAO.Recordset
    Dim TestValue As String
    Dim strSQL2 As String


    TestValue = BfrUpD
    'Debug.Print TestValue
    strSQL2 = "SELECT * FROM Table1 WHERE (((Table1.ID)>" & TestValue & "))"
    'Debug.Print strSQL2


    Set dbPP1 = OpenDatabase("C:\filepath\Database11.mdb")
    Set qryPP1 = dbPP1.OpenRecordset(strSQL2)

    Set WBout = Workbooks.Open("C:\filepath\h.xlsm")
    Set WSout = WBout.Sheets("sheet1")
    WSout.Range("A1").End(xlDown).Offset(1, 0).CopyFromRecordset qryPP1

    qryPP1.Close
    dbPP1.Close
    WBout.Save
    WBout.Close

    MsgBox "Data copied. Thank you."

Set WBout = Nothing
Set WSout = Nothing
Set dbPP1 = Nothing
Set qryPP1 = Nothing
Set ResTemp1 = Nothing

End Sub
Dim dbPP As DAO.Database
    Dim ResTemp As DAO.Recordset
    Dim z As Long
    Dim SelectLast As String

    SelectLast = "SELECT Max(Table1.ID) AS MaxOfID FROM Table1"
    'Debug.Print SelectLast

    Set dbPP = OpenDatabase("C:\filepath\Database11.mdb")
    Set ResTemp = dbPP.OpenRecordset(SelectLast)
    z = ResTemp(0) 'specifying it's array location (I think) - there is only one
    'item in this result, so it will always be (0)

    AftUpD = z
    'Debug.Print AftUpD
    ResTemp.Close
    dbPP.Close



Set dbPP = Nothing
Set ResTemp = Nothing
'Set SelectionLast = Nothing
'z = Nothing

access vba - How to get the last record id of a form? - Stack Overflow

access-vba
Rectangle 27 0

Instead of

ws1.Range("V1").Copy ws2.Range("A" & DestRow)
ws1.Range("V1").Copy
ws2.Range("A" & DestRow).PasteSpecial Paste:=xlPasteValues

excel - Copy cell range from one worksheet and paste in a different wo...

excel excel-vba