Excell copy from one workbook to another

[DocCon]

Hi I am new to macros basic knowledge.I have updated a template form an older version. My users have been using oa copy of the old version. The new version has just wording changes and 10 cells were added on one sheet. All of the information is in the same cells as old version. The added cells have not been used.Is there a way to export or copy just the data from the old version minus the ten cells.

Thanks Doc

 

[SkipVought]

Hi,

How about copy the entire thing and then clear the range that you did not want to copy???



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[fredericofonseca]

Some VBA code.

It it not exactly what you need, but should be enough to allow you to do it on your own.

Private wWork_sheet As Worksheet
Private wWork_values As Worksheet
Private iWorkingRow As Integer
Private iWorkingRow2 As Integer

Set wWork_values = Workbooks("mydoc.xls").Worksheets("Work_values")



Private Sub copy_work_records(sWorkbook As String)
Dim iRow As Integer
Dim iCol As Integer
Dim temp As String
    temp = wWork_values.UsedRange.Address

    Set wWork_sheet = Workbooks(sWorkbook).Worksheets(1)
'
    wWork_sheet.Cells.SpecialCells(xlLastCell).Select
    iRow = wWork_sheet.Cells.SpecialCells(xlLastCell).Row
    iCol = wWork_sheet.Cells.SpecialCells(xlLastCell).Column
    
    wWork_sheet.Range(Cells(1, 1), Cells(iRow, iCol)).Select
    wWork_sheet.Range(Cells(1, 1), Cells(iRow, iCol)).Copy
    wWork_values.Activate
    wWork_values.Select
    wWork_values.Cells(iWorkingRow2, 1).Select
    wWork_values.Paste
    iWorkingRow2 = iWorkingRow2 + iRow
    
    Set wWork_sheet = Nothing

End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

Frederico,

Nice suggestion. Here's how it can be streamlined a bit...

Private Sub copy_work_records(sWorkbook As String)
    Dim lWorkingRow2 As Long
'don't know where wWork_values comes from ?????
    With wWork_values.UsedRange
        lWorkingRow2 = .Row + .Rows.Count
    End With

    With Workbooks(sWorkbook).Worksheets(1)
        .UsedRange.Copy _
            Destination:=wWork_values.Cells(iWorkingRow2, 1)
    End With

End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[fredericofonseca]

Skip,

I did try doing like that, but it didn't work.
The only way I managed to make it work was by activating the destination sheet.


---
wWork_values is defined outside the sub

Set wWork_values = Workbooks("mydoc.xls").Worksheets("Work_values")


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

like I stated in the COMMENT
[tt]
don't know where wWork_values comes from ?????
[/tt]
IF wWork_values were decalred as a PUBLIC vaiable AND Set in the Calling program, it would have worked as written.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[fredericofonseca]

this is VBA, so it is within Excel itself.
Running as a macro.

The original code was copying from within the same workbook, different worksheets.

Definitions were (as mentioned on the original code I post)

Private wWork_sheet As Worksheet
Private wWork_values As Worksheet
Private iWorkingRow As Integer
Private iWorkingRow2 As Integer

In the main sub wWork_values and wWork_sheet are both set as
Set wWork_values = Workbooks("mydoc.xls").Worksheets("Work_values")


Being public or private did not make any difference.

Not an issue, as the whole thing is working and going to production Monday.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com