Excel Howto?
Excel VBA how to?

-Aviral Mittal




1. Copy values from a column in 1 sheet to another sheet.
Use the function getColValues, in the previous Page, to get the column in a string, then split the string using
the separator char, and finally dump values into another Column in another Sheet.
Note This Function only copies values, not the format. There are easy and quick ways to copy+paste whole
column from sheet1 to sheet2 using VBA.  But this code is presented for the user to learn VBA.

'------------------------
Function copyColValuesSht1ToSht2(ColNo As Long, srcShtName As String, targShetName as String, Optional stRow As Long, Optional strSeparater as string) As String
  'This sub will copy Column referred to by ColNo from srcShtName to same ColNo in targShtName
  'Example Use 1:Call copyColValuesSht1ToSht2(1, "Sheet1", "Sheet3", 1, "#")
  Dim colStr As String
  Dim elem As Variant
  Dim rRow As Long
  rRow = 0
  If (strSeparater = "") Then
    strSeparater = ","
  End If
  If (stRow = 0) Then
    stRow = 1
  End If
 
  colStr = getColValues(ColNo, srcShtName, "#", 1)
 
  For Each elem In Split(colStr, "#")
    rRow = rRow + 1
    ThisWorkbook.Sheets(targShtName).Cells(rRow, ColNo).Value = elem
  Next elem
  MsgBox "Copied Column " & ColNo & " from Sheet: '" & srcShtName & "' To Sheet: " & targShtName & "'"
End Sub
Sub try_copyColValuesSht1ToSht2()
  Call copyColValuesSht1ToSht2(1, "Sheet1", "Sheet3", 1, "#")
End Sub

Download the WB with the above code:
Book6.xlsm


<- Previous                                                                                            Next ->

Key Words: Excel VBA: Copy Entire Column from 1 sheet to other