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

lydmanen · #1 (**permalink**) 09-13-04, 14:23

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

ray705 · #2 (**permalink**) 09-13-04, 15:18

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.

shades · #3 (**permalink**) 09-13-04, 15:20

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.

lydmanen · #4 (**permalink**) 09-13-04, 15:52

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

lydmanen · #5 (**permalink**) 09-13-04, 15:55

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

DavidCoutts · #6 (**permalink**) 09-14-04, 08:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech