CODEWITHSUNDEEP
vbaexcel
user1750089
user1750089

Reputation: 11

How do you copy cell data in a range from one sheet to specific cells in another sheet?

The issue I am currently having is trying to write a subroutine that will copy cells for example A1:A10 from one worksheet; to another sheet with specific cells like A1,A2,B1,B2,B3,B5,C1,C2,C4,C5. The issue is the current sub I have cannot read in the worksheet name and range values. Here is the code

'User will be able to copy cell data from one to another.
Public Sub CopyCell(ByVal pv_worksheet_source As Worksheet, _
                    ByVal pv_range_source_cells As Range, _
                    ByVal pv_worksheet_destination As Worksheet, _
                    ByVal pv_range_destination_cells As Range, _
                    ByRef pr_str_error_message As String)

'first the cells are compared for the data that is in them.
If pv_range_source_cells.Cells.Count <> pv_range_destination_cells.Cells.Count Then
     pr_str_error_message = pr_str_error_message & "The cell count " & pv_range_source_cells.Cells.Count & " and " & _
        pv_range_destination_cells.Cells.Count & " do not match. The cells of Initial Optics Input and Output cannot be copied!"
    Exit Sub
End If


Dim source_cells As Range
Dim i As Integer
Dim ArrOut() As String
Dim str_source_worksheet_name As String
Dim str_destination_worksheet_name As String



ArrOut() = Split(pv_range_destination_cells.Address, ",")

i = 0


For Each source_cells In pv_worksheet_source
    pv_worksheet_destination.Range(ArrOut(i)).Value = source_cells.Value
    i = i + 1
Next
End Sub

As you can see the code is suppose read in source and destination sheet names, and their cells ranges for copying. There are type mismatches, plus I cannot figure out how else to copy the data over in VBA. This sub must be kept in the module format because it will be called throughout the Workbook for other sheets. Here is an example of it being called on the source worksheet....

Private Sub CopyButton_Click()
'User gets data copied over to specific cells
   Dim str_error_message As String
    Call CopyCell(Sheet1, _
                  "A1:A10", _
                  Sheet2, _
                  "B1,B2,B3,C1,C2,C3,C4,D1,D2,D3", _
                  pr_str_error_message:=str_error_message)


End Sub

This is where the error begins when I click the button. Please help because I have been dealing with this problem for days now! I would really appericate it! :)

Upvotes: 0

Views: 2371

Answers (1)

scott
scott

Reputation: 2275

ArrOut should be an array, not a string.

Changing 

    Dim ArrOut as String

to

    Dim Arrout as Variant

then set the array like this:

   ArrOut = Split(pv_range_destination_cells, ",")

then call the "paste"

   For Each source_cells In pv_worksheet_source
       for i = lbound(arrout) to ubound(arrout)
          pv_worksheet_destination.Range(ArrOut(i)).Value = source_cells.Value
       next i
   Next

edit: you are also setting pv_destination_cells as a string in your call in copybutton click but declaring it as a range in the sub. you should set it as a string in the original sub if you are going to call it as a string like in your example and then leave out the ".address" from the array declaration as i've reflected in my code above

Upvotes: 2

Related Questions