MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

write data from one workbook to another

  Asked By: Henry    Date: Sep 02    Category: MS Office    Views: 2871

I have a workbook with multiple sheets. I get data from these
sheets one sheet at a time. I need to write certain cells from
certain rows on some of the sheets to a sheet in another workbook.
This will be uploaded to our AS/400 for processing( That's the easy
part :) )
How do i referance the other workbook to access the sheet?
Also, how do I insert a new row to place these cells into?

Option Base 1

Dim wbkBudget As Workbook
Dim mySheet As Worksheet
Dim wbkOuput As Workbook
Dim outSheet As Worksheet

Private Type accntRec
glNumber As Integer
store As Integer
year As Integer
monthAmt(12) As Currency
End Type

Dim macctRec As accntRec

Private Sub cmdSave_Click()

Set wbkBudget = ActiveWorkbook
Set wbkOutput = "C:\BudgetOuput.xls"

For Each mySheet In wbkBudget.Worksheets
If mySheet.Cells(1, 6) Then ' checks for store number
Call CheckSheet
End If
Next mySheet

End Sub

Public Sub CheckSheet()
Dim i As Integer

For i = 1 To 43 'rows in sheet
If mySheet.Cells(i, 1) > 0 Then 'checks for gl number
With macctnumber
.glNumber = mySheet.Cells(i, 1).Value
.store = mySheet.Cells(1, 6).Value
.year = mySheet.Cells(1, 4).Value
.monthAmt(1) = mySheet.Cells(i, 3).Value
.monthAmt(2) = mySheet.Cells(i, 5).Value
.monthAmt(3) = mySheet.Cells(i, 7).Value
.monthAmt(4) = mySheet.Cells(i, 12).Value
.monthAmt(5) = mySheet.Cells(i, 14).Value
.monthAmt(6) = mySheet.Cells(i, 16).Value
.monthAmt(7) = mySheet.Cells(i, 21).Value
.monthAmt(8) = mySheet.Cells(i, 23).Value
.monthAmt(9) = mySheet.Cells(i, 25).Value
.monthAmt(10) = mySheet.Cells(i, 30).Value
.monthAmt(11) = mySheet.Cells(i, 32).Value
.monthAmt(12) = mySheet.Cells(i, 34).Value
'how do i write these to another sheet in a
different workbook
End With

End If
Next i

End Sub



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on write data from one workbook to another Or get search suggestion and latest updates.