Copy and reformat cells from one worksheet to another

Question

I'm putting together a series of macros and have reached the end of my skillset in VBA (which is limited to begin with).

I have two sheets with the second being dependent on the first for its content. Currently, users are required to copy and insert the content from the first sheet into the second and then manipulate it, which, understandably, nobody does because they're either lazy or intimidated.

To make it more likely that they will copy the information to the second sheet, I want to use a macro called from a button.

There are essentially two issues I need to overcome, and I'm praying that you'll be able to help.

1. The region to be copied is not static and will vary in the number of rows that need to be copied; and
2. Not all of the columns in the copied range need to be inserted into the destination sheet.

To address the first issue, I know that I will need to use some variation of the LastrRow used as provided in this answer: https://stackoverflow.com/a/11169920/4693144

I've tried using that code as follows, but it keeps kicking "Error '9': Subscript out of range" back at me:

Sub CopyBudget()

Dim LastRow As Long
Dim CopyRange As Range
    With Sheets("Project Budget")
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        LastRow = .Cells.Find(What:="*", _
                After:=.Range("A8"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlFormulas, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

    Else
        LastRow = 1
    End If
 End With

 Sheets("Project Plan").Range("D60").Resize(LastRow) = _
            Sheets("Project Budget").Range("A8").Resize(LastRow).Value
                If Not CopyRange Is Nothing Then
                CopyRange.Copy Sheets("Project Plan").Cells(60, "D")
            End If


End Sub

EDIT: My apologies for not posting my error reference when I originally wrote the post (I was running out the door at work). The Error highlights the entire block of code:

        LastRow = .Cells.Find(What:="*", _
                After:=.Range("A8"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlFormulas, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

I realise that this code would only serve to copy the range itself and not to differentiate between the columns. The issue is that I'm not sure how to go about ensuring that the copied rows only contain the desired columns. Should I just use several variations of the functional version of the code above based on the desired columns running in series to copy and insert each column independently, or should I just grab it all and then use code to select the columns I want from the copied or inserted cells?

I'm really looking for as much help on this as I can get.

Answer 1

For your problem to copy only specific columns, I think it'll be harder to just use a range.

You can try to copy the selected range on a buffer sheet that will be scanned to delete useless columns and then do the copy to final sheet. You can use this for columns deletion :

Sub Column_Delete(ByVal Sheets_Index As Integer, ByVal Str_to_Find As String)

Dim Ws As Worksheet
Set Ws = Worksheets(Sheets_Index)


Dim EndColumn As Integer
EndColumn = Ws.Cells(1, Columns.Count).End(xlToLeft).Column

'descending travel of the columns as we are going to delete some of them
For j = 1 To EndColumn
    If InStr(Ws.Cells(1, EndColumn - j + 1), Str_to_Find) > 0 Then
        Ws.Columns(EndColumn - j + 1).EntireColumn.Delete Shift:=xlToLeft
    End If
Next j

'Don't forget to free Ws (like I did...)
Set Ws = Nothing

End Sub

Function ColLet(x As Integer) As String
With ActiveSheet.Columns(x)
    ColLet = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
End With
End Function

And to copy paste, you don't use your range in the code you gave, personnaly I use this pretty often :

    Sub AddDataToAnotherSheet(ByVal DataSheetName As String, ByVal StackSheetName As String)
'
Dim ShIn As Worksheet
Dim ShOut As Worksheet
Set ShIn = ThisWorkbook.Sheets(DataSheetName)
Set ShOut = ThisWorkbook.Sheets(StackSheetName)

'ShIn.Cells(2, 1).End(xlToRight).Column
Dim RgTotalInput As String
Dim RgTotalOutput As String

RgTotalInput = "$A$2:$" & ColLet(ShIn.Cells(1, 1).End(xlToRight).Column) & "$" & ShIn.Cells(Rows.Count, 1).End(xlUp).Row
RgTotalOutput = "$A$" & ShOut.Cells(Rows.Count, 1).End(xlUp).Row + 1

ShIn.Range(RgTotalInput).Copy Destination:=ShOut.Range(RgTotalOutput)


End Sub



Function ColLet(x As Integer) As String
With ActiveSheet.Columns(x)
    ColLet = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
End With
End Function

Answer 2

A. So, generally speaking, if what you are moving are values (including the resultant value computed by a formula, but not the formula itself) from your first sheet to the second sheet, you should use a VBA assignment statement to copy values, not cut-and-paste. It will work faster for moderate sets of cells to copy, you will have complete control of the rows and columns to copy over (especially if certain rows and/or columns are not to be copied), it is more understandable to programmers after-the-fact, is much simpler to configure, the code will usually be much smaller, and is much easier to debug.

So if we assume you have pre-defined two ranges, srcRng and destRng on the two different sheets, all you do is something equivalent to the below inside usually two nested loops:

destRng.Cells(destRow, destCol).value = srcRng.Cells(srcRow, srcCol).value

Your outer loop is usually for the rows to copy, your inner loop is usually for the columns you want to copy. Of course, if there are only two or three columns to copy, just don't make the inner loop, and just repeat the above copy statement those two or three times with different destCol/srcCol pairs.

B. If you indeed need to copy the formulas themselves, and you need the formulas to auto-adjust their relative references to other copied data, then indeed you are forced to use Excel's cut-and-paste since only that does formula relative-reference adjustment. Your strategy for dealing with that is whether there are more rows to skip or more columns to skip ... if it is all rows (even a variable number of rows), then it would be best to select and create copy ranges of the columns to move that span all the rows.

C. In terms of debugging help, you really must tell us which line the failure is occurring on. The VBA debugger gives you which line is failing by highlighting the code line. Worst-case just put in a breakpoint and step through your subroutine to find it. But virtually everyone that asks questions here fails to provide that obvious necessary information.

D. The following line seems improperly composed. Cells property takes numeric values, not string value. Column "D" is 4.

CopyRange.Copy Sheets("Project Plan").Cells(60, "D")