troubleshooting Question

Repositioning copy-paste operations from simple (full row or full columns) to targeted cells

Avatar of donohara1
donohara1 asked on
Microsoft Excel
5 Comments1 Solution268 ViewsLast Modified:
I have been given a large spreadsheet with many VBA macros to convert (to a reformatted source workbook).
Below is am example of a simple copy & paste operation, Using the new source I have to target the paste target to start on row 3 (vs row 1). So I have to convert the .Columns operator to a  .Cells operator. For example:  change:

Set r = thwb.Sheets(8).Columns("p")   (r is a range,  thwb is this workbook used as a target)

   for example:
Set r = thwb.Sheets(8).Cells("p3")    to start the Paste on Row 3.
Set SourceSh = Sourcewb.Sheets("my data")
                    r.PasteSpecial xlPasteValues, , , False

When I do this, I get the error msg:  ' application or object designed error '

What am I not doing correctly?


Sample code (after changing from .Columns to .Cells ):

Sub Stocks()

    Dim Sourcewb As Workbook
    Dim SourceSh As Worksheet
    Dim thwb As Workbook
    Dim r As Range
        Set thwb = ThisWorkbook
        Set r = thwb.Sheets(8).Cells("p3")
On Error GoTo errhandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
        FilePath = "C:\Users\DonO\Documents\FIRMS\Test\QRM\"
        Filename = Dir(FilePath & "*.xlsx")
        Do While Filename <> ""
  '   only one workbook in source directory.
            If Filename = thwb.Name Then GoTo nxt
            If Filename & ".xlsx" = thwb.Name Then GoTo nxt
                Set Sourcewb = Workbooks.Open(FilePath & Filename, False)
                    Set SourceSh = Sourcewb.Sheets("Risk by Securities")
                    r.PasteSpecial xlPasteValues, , , False
        Set r = thwb.Sheets(8).Cells("aa3")
                    r.PasteSpecial xlPasteValues, , , False
        Set r = thwb.Sheets(8).Cells("x3")
                    r.PasteSpecial xlPasteValues, , , False
        Set r = thwb.Sheets(8).Cells("af3")
                    r.PasteSpecial xlPasteValues, , , False
        Set r = thwb.Sheets(8).Cells("ad3")
                    r.PasteSpecial xlPasteValues, , , False
        Set r = thwb.Sheets(8).Cells("z3")
                    r.PasteSpecial xlPasteValues, , , False
            Workbooks(Filename).Close SaveChanges:=False
                Filename = Dir
    Set Sourcewb = Nothing
    Set SourceSh = Nothing
    Set thwb = Nothing
    Application.ScreenUpdating = True
    Application.EnableEvents = True
        If Err.Number <> 0 Then
            MsgBox "An error has occurred." & vbNewLine & Err.Description
        End If
End Sub
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros