Macro Import Excel Sheet into Excel Sheet

[Guest]

I have an Excel Workbook, with sheets Intro and Data.

I am trying to write a macro that will
.. Clear data in sheet [Data]
.. Open another excel file in the same directory called "Fixed Calls.xls" and
copy the data from sheet [Sheet1] column A to AA to the current workbook
sheet [Data] columns A to AA.
.. Add a new field in column AB for all rows used that will add Z to A

I'm a novice so I don't even know how to do the import without using a
WebQuery, however I want to do this via a macro.

any ideas?

 

[Guest]

This is not a difficult task to write. If you want to learn to write macros
the easiest way is to use the macro recorder and record doing it once. then
look at the code the recorder makes. It does not write the best code but it
will give you a good start. From there you can modify it to do exactly what
you want.

Even after years of programming Excel Sometimes I have to use the macro
recorder to see how it will do something and get pointers to how to do it.

Peter Richardson

 

[Guest]

yes I use the recorded sometimes but I don't see how it would help me here.

Is there a macro somewhere that does something similar that I can use as a
starting point?

This is not a difficult task to write. If you want to learn to write macros
the easiest way is to use the macro recorder and record doing it once. then
look at the code the recorder makes. It does not write the best code but it
will give you a good start. From there you can modify it to do exactly what
you want.

Even after years of programming Excel Sometimes I have to use the macro
recorder to see how it will do something and get pointers to how to do it.

Peter Richardson

I have an Excel Workbook, with sheets Intro and Data.

I am trying to write a macro that will
. Clear data in sheet [Data]
. Open another excel file in the same directory called "Fixed Calls.xls" and
copy the data from sheet [Sheet1] column A to AA to the current workbook
sheet [Data] columns A to AA.
. Add a new field in column AB for all rows used that will add Z to A

I'm a novice so I don't even know how to do the import without using a
WebQuery, however I want to do this via a macro.

any ideas?

 

[Guest]

Well I followed the steps you outlined and recorded it.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/12/2007 by Peter Richardson
'

'
Sheets("data").Select
Cells.Select
Range("A10").Activate
Selection.ClearContents
Range("A1").Select
Workbooks.Open Filename:= _
"C:\Documents and Settings\par\My Documents\fixed calls.xls"
Columns("A:AA").Select
Selection.Copy
Windows("Book1").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

A couple quick changes and I ended up with:
Sub Macro2()
Dim currRow As Long
Sheets("data").Select
Cells.Select
Selection.ClearContents
Workbooks.Open Filename:="fixed calls.xls"
Columns("A:AA").Select
Selection.Copy
Windows("Book1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
For currRow = ActiveSheet.UsedRange.Row To ActiveSheet.UsedRange.Row +
ActiveSheet.UsedRange.Rows.Count
Cells(currRow, 28) = "=sum(A" & currRow & ":AA" & currRow & ")"
Next
End Sub


This should give you a starting place.

yes I use the recorded sometimes but I don't see how it would help me here.

Is there a macro somewhere that does something similar that I can use as a
starting point?

This is not a difficult task to write. If you want to learn to write macros
the easiest way is to use the macro recorder and record doing it once. then
look at the code the recorder makes. It does not write the best code but it
will give you a good start. From there you can modify it to do exactly what
you want.

Even after years of programming Excel Sometimes I have to use the macro
recorder to see how it will do something and get pointers to how to do it.

Peter Richardson

I have an Excel Workbook, with sheets Intro and Data.

I am trying to write a macro that will
. Clear data in sheet [Data]
. Open another excel file in the same directory called "Fixed Calls.xls" and
copy the data from sheet [Sheet1] column A to AA to the current workbook
sheet [Data] columns A to AA.
. Add a new field in column AB for all rows used that will add Z to A

I'm a novice so I don't even know how to do the import without using a
WebQuery, however I want to do this via a macro.

any ideas?