Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    22

    Angry Unanswered: Its only a simple copy-paste macroo!! noo!! :(

    Hi there,

    I have a little (or big?) problem con a simple copy-paste macro, what I'm trying to do is to copy an specific range for one workbook (that is on a different file what I'm working), and paste it to an specific worksheet, first empty cell on a column on my actual workbook. I was trying a lot but it send me an strange "400" error msg (just 400), and because of that I'm trying to make a "cell by cell" move data (with out paste method), but doesn't work, could you guys plz help me to find a solution?

    Code:
    Sub Actualiza()
    
    ActiveWorkbook.Unprotect
    
    Dim myCurrentWorkbook As Workbook
    Dim myDataSourceWorkbook As Workbook
    Dim myCurrentWorksheet As Worksheet
    
    Set myCurrentWorkbook = ActiveWorkbook
    
    
    FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    Set myDataSourceWorkbook = Workbooks.Open(FileToOpen)
    
    myDataSourceWorkbook.Sheets(1).Range("C2:F2").Copy
    
    Application.DisplayAlerts = False
    myDataSourceWorkbook.Close
    Application.DisplayAlerts = True
    
    With myCurrentWorkbook.Sheets("H-M")
    'Range("D1").End(xlDown).Offset(1, 0).PasteSpecial xlValues
    'With myCurrentWorkbook.Sheets("H-M")
    .Select
    .Range("D1").Select
    
    .Cells(ActiveCell.End(xlDown).Offset(1, 0).Row, 4).Select
    ActiveCell.PasteSpecial xlPasteAll
    End With
    
    ActiveWorkbook.Protect Structure:=True, Windows:=False
    
    MsgBox "Completada la Tarea", vbOKOnly
    
    End Sub

    ... And for the cell by cell method Im trying this: (it send me an error message regards the F1 variable, Im trying to set F1 as string but still doesn't work)
    Code:
    Sub Actualiza7()
    Dim F1 As Singe, F2 As Single, F3 As Single, F4 As Single
    Dim myDataSourceWorkbook As Workbook
    Dim myCurrentWorkbook As Workbook
    Dim FileToOpen As String
    Dim myDataSourceWorksheet As Worksheet
    Dim myCurrentWorksheet As Worksheet
    
    Set myCurrentWorkbook = ActiveWorkbook
    
    FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    If FileToOpen = "false" Then Exit Sub
    
    Set myDataSourceWorkbook = Workbooks.Open(FileToOpen)
    Set myDataSourceWorksheet = myDataSourceWorkbook.Sheets("Hoja2")
    Set F1 = myDataSourceWorksheet.Cells(2, 3).Value
    Set F2 = myDataSourceWorksheet.Cells(2, 4).Value
    Set F3 = myDataSourceWorksheet.Cells(2, 5).Value
    Set F4 = myDataSourceWorksheet.Cells(2, 6).Value
    
    Application.DisplayAlerts = False
    myDataSourceWorkbook.Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False
    
    Set myCurrentWorksheet = myCurrentWorkbook.Sheets("H-M")
    Set myCurrentWorksheet.Range("D1").End(xlDown).Offset(1, 0).Value = F1
    Set myCurrentWorksheet.Range("E1").End(xlDown).Offset(1, 0).Value = F2
    Set myCurrentWorksheet.Range("F1").End(xlDown).Offset(1, 0).Value = F3
    Set myCurrentWorksheet.Range("G1").End(xlDown).Offset(1, 0).Value = F4
    
    Application.ScreenUpdating = True
    End Sub
    thanks a lot for ur helping
    David Garcia

  2. #2
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44
    The first example is OK, just take out the close statement.
    Code:
    myDataSourceWorkbook.Close
    Instead use:
    Code:
        Workbooks(myCurrentWorksheet).Activate
    After you paste the data, turn off the CutCopyMode.
    Code:
        Application.CutCopyMode = False
    When you are done with the paste operation, then close the source.

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    I haven't looked at all of the code, but one of the first pieces of advice for writing variables is to NEVER use a keyboard shortcut (F1, F2, etc.) as a variable.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Aug 2004
    Posts
    22

    Thanks, but now send me another error msg

    Hi!

    Thnks for ur comments, I was doing what u tell me but know send me a different error statement (that's good , I really hate this error msg that only have the 400 number), the error msg is "The types don't agree"... I think this is a more esay error msg than 400.

    my code by now is here, if u could help me I'll appreciated it,

    Code:
    Sub Actualiza()
    
    ActiveWorkbook.Unprotect
    
    Dim myCurrentWorkbook As Workbook
    Dim myDataSourceWorkbook As Workbook
    Dim myCurrentWorksheet As Worksheet
    
    Set myCurrentWorkbook = ActiveWorkbook
    
    
    FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    Set myDataSourceWorkbook = Workbooks.Open(FileToOpen)
    
    myDataSourceWorkbook.Sheets(1).Range("C2:F2").Copy
    
    Application.DisplayAlerts = False
    Workbooks(myCurrentWorksheet).Activate
    Application.DisplayAlerts = True
    
    With myCurrentWorkbook.Sheets("H-M")
    .Select
    .Range("D1").Select
    
    .Cells(ActiveCell.End(xlDown).Offset(1, 0).Row, 4).Select
    ActiveCell.PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End With
    
    ActiveWorkbook.Protect Structure:=True, Windows:=False
    
    MsgBox "Completada la Tarea", vbOKOnly
    
    End Sub
    Thanks for ur comments
    David

  5. #5
    Join Date
    Aug 2004
    Posts
    22

    Red face Hi Shade

    Hi Shades!

    I do what u telling me, but still doesn't work, but thank you for this comment , I think it give me more accurancy in the order what is my problem store

    Thanks, and if u have another suggestion I'll appreciate it

    David García

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi David
    your problem lies here
    Workbooks(myCurrentWorksheet).Activate
    I presume you meant myCurrentWorkBook rather then Worksheet
    2 reasons myCurrentWorkBook is an object and cant be referenced like this
    myCurrentWorksheet is not set anywhere so it's value is Nothing
    What i think you want is
    myCurrentWorkBook.Activate

    here is your code again with some minor revisons
    Code:
    Sub Actualiza()
    
        ActiveWorkbook.Unprotect
        
        Dim myCurrentWorkbook As Workbook
        Dim myDataSourceWorkbook As Workbook
        Dim myCurrentWorksheet As Worksheet
        
        Set myCurrentWorkbook = ActiveWorkbook
        Set myCurrentWorksheet = myCurrentWorkbook.Worksheets("H-M")
        
        FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
        Set myDataSourceWorkbook = Workbooks.Open(FileToOpen)
        
        myDataSourceWorkbook.Sheets(1).Range("C2:F2").Copy
        
        Application.DisplayAlerts = False
        myCurrentWorkbook.Activate
        Application.DisplayAlerts = True
        
        With myCurrentWorksheet
            If Not IsEmpty(.Range("D2")) Then
                .Cells(.Range("D1").End(xlDown).Offset(1, 0).Row, 4).PasteSpecial xlPasteAll
            Else
                .Range("D2").PasteSpecial xlPasteAll
            End If
            Application.CutCopyMode = False
        End With
        
        ActiveWorkbook.Protect Structure:=True, Windows:=False
    
        MsgBox "Completada la Tarea", vbOKOnly
    
    End Sub
    HTH
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •