Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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)
   to:

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

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

What am I not doing correctly?

Thanks,
Don


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")
Err.Clear
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")
                    SourceSh.Columns("a:d").Copy
                    r.PasteSpecial xlPasteValues, , , False
       
        Set r = thwb.Sheets(8).Cells("aa3")
        SourceSh.Columns("e:g").Copy
                    r.PasteSpecial xlPasteValues, , , False
       
       
        Set r = thwb.Sheets(8).Cells("x3")
        SourceSh.Columns("i:j").Copy
                    r.PasteSpecial xlPasteValues, , , False
       
       
        Set r = thwb.Sheets(8).Cells("af3")
        SourceSh.Columns("k:s").Copy
                    r.PasteSpecial xlPasteValues, , , False
       
       
        Set r = thwb.Sheets(8).Cells("ad3")
        SourceSh.Columns("j").Copy
                    r.PasteSpecial xlPasteValues, , , False
       
       
        Set r = thwb.Sheets(8).Cells("z3")
        SourceSh.Columns("s").Copy
                    r.PasteSpecial xlPasteValues, , , False
       
            Workbooks(Filename).Close SaveChanges:=False
nxt:
                Filename = Dir
        Loop
     
    Set Sourcewb = Nothing
    Set SourceSh = Nothing
    Set thwb = Nothing
errhandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
        If Err.Number <> 0 Then
            MsgBox "An error has occurred." & vbNewLine & Err.Description
        End If
        Sheets("Control").Select
    Range("A1").Select
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers