VBA - Tricky copying of cells

[oggy3000]

Hey guys,
I am using Excel 2003 and have a small problem regarding copying of data because my knowledge of VBA is limited:

In the sheet "Transfer" there is a changing amount of filled rows in the columns A to G which I want to copy with a button in 2 ways to 2 destinations.

The first destination is the sheet "Outbound" in the same workbook. For this case, I want to copy columns A to F (except the first row) from "Transfer" to D to I in "Outbound" but in a way that the entries are stored under the ones that are already in the sheet. Column G contains a postive number which I want to be transfered negatively to column J (e.g. 10 shall become -10).

The second destination is a different workbook called "Customs Blanko.xls". Into this I want to copy the data in A to G of "Transfer" starting in cell A9. The Problem here is that there is data above and below to the future copy destination which should not be overwritten. The cells next to the copy area should contain formulas which I would like to be extended according to the number of copied rows.

I used the follwing code for a copy operation on a different sheet where it worked fine. Unfortunately I am not able to adjust the copy range to be variable. Also the extended copy features (negative copy of column G) are not included yet.

Private Sub cmdCopy_Click()
Dim Zei1 As Long, wks1 As Worksheet
Set wks1 = Worksheets("Filtering")
    With Worksheets("Transfer")
        Zei1 = .Cells(Rows.Count, 1).End(xlUp).Row
    If .Cells(1, 1) = "" Then Zei1 = 0
        wks1.Range("A5:F5").Copy Destination:=.Cells(Zei1 + 1, 1)
    End With
End Sub

I hope that someone can help me with this If required, I can upload a version of my file.

With best regards from Germany
Jan

 

[oggy3000]

Hey its me again,
I found some code that will help me a lot for the 2nd part. Unfortunately I am not able to alter it for my needs. Maybe one of the Pros out there can help me?
Here is the original code:

Private Sub Button1_Click()
    Dim wbk As Workbook
    Dim strSecondFile  As String
    Dim strFirstFile As String
    strFirstFile = "c:\asdf.xls"
    strSecondFile = "c:\vbf.xls"
 
        Set wbk = Workbooks.Open(strFirstFile)
        With wbk.Sheets("Sheet1")
            .Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Copy
        End With
 
        Set wbk = Workbooks.Open(strSecondFile)
        With wbk.Sheets("Bondout List")
            Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Insert shift:=xlDown
        End With
End Sub

My problem is that I dont want to open the first file because that is the one I am running the Button on a UserForm from. So I just want to copy and insert my data into the second file. After my alteration I always get "Run-time Eror 9: Subscript out of range"

Hope someone can help me with this.

Cheers
Jan

 

[Sektor]

Don't forget dots when using With...End With.

Private Sub Button1_Click()
    Dim wbk As Workbook
    Dim strSecondFile  As String
    Dim strFirstFile As String
    strFirstFile = "c:\asdf.xls"
    strSecondFile = "c:\vbf.xls"
 
        Set wbk = Workbooks.Open(strFirstFile)
        With wbk.Sheets("Sheet1")
            .Range("A2:G" & [B][COLOR="Red"][SIZE="4"].[/SIZE][/COLOR][/B]Range("A" & Rows.Count).End(xlUp).Row).Copy
        End With
 
        Set wbk = Workbooks.Open(strSecondFile)
        With wbk.Sheets("Bondout List")
            [B][COLOR="Red"][SIZE="4"].[/SIZE][/COLOR][/B]Range("A2:G" & [B][COLOR="Red"][SIZE="4"].[/SIZE][/COLOR][/B]Range("A" & Rows.Count).End(xlUp).Row).Insert shift:=xlDown
        End With
End Sub

 

[oggy3000]

Thanks for the advice Sektor Changed it immediately.
What would I have to change in order to run the script directly from asdf.xls (so dont open asdf.xls and so on). So just copy the lines from the sheet and insert them into the other workbook.
At the moment I can only run it from a 3rd sheet but that is not exactly what I want/need...

Cheers
Jan