Excel Macros - Help Needed

bigup

Hi Guys,

Firstly, I do not have a clue about Excel or how to use macros, but from the complex stuff i that have read on the net, i assume that there is a simple way of automating the following:

Trasferring line 2 of "Spreadsheet1" in Worbook X to The next blank line in "Spreadsheet1" in Workbook Y

It will always be line 2 and only one line will need transferring. All fields and headings etc are the same in both workbooks.

As i have to do this transfer many times a day from individual workbooks into one central Excel workbook, does anyone know the macro needed for this and how i use it?

Many thanks for any info

LincolnshireYokel

Essentially, what you can do is 'record' the keystrokes required to perform a task, and then activate those keystrokes using a hot key combination. You can then record a second macro that executes the first macro many times.

Now, ive never tried switching workbooks using a macro, because macros are tied to the workbook they are created in.. You might have to use a multi page spreadsheet instead, ive always done it that way.

Have a look at this:

http://www.youtube.com/watch?v=KKJj78LoFaA

and tell us if it is any help

Thers also this one

http://youtu.be/lfm7ns8ZC6E

that tells you how to do Vlookups between workbooks, so it shows how he switches workbooks within ther macro.

rmg1

Will both workbook be open at the same time or will workbook "Y" need opening and closing?

victor2

Are you consolidating the data from many spreadsheets into one? If so, and these spreadsheets already exist, as against being newly created each time, then a simple formula can reference other spreadsheets/workbooks.

Perhaps if you outline what you are trying to achieve i a little more detail, there's a relatively easy way to achieve the end result without the need for macros.

johnmc

I would use a combination of the above.

On a seperate tab (you can hide it if you wish) do a simple link to each cell on the other spreadsheet.

Record a macro of keystrokes using <ctrl><C> to copy, <ctrl><(down arrow)> and then <ctrl><V> to paste.

Assign that to a button, if you wish.

Next task is to determine whether it has changed before you paste it....................

LucianH

I don't think you can do this by simply using the "record maco" since it needs to copy the row into the next blank line (which will obviously differ each time). You therefore need a macro to find the next blank line - ie:

Option Explicit

Sub DoIt()
Dim ThisPath As String
Dim SourceWbName As String
Dim SourceWsName As String
Dim DestWsName As String
Dim DestWs As Worksheet
Dim DestRow As Range

ThisPath = ActiveWorkbook.Path
SourceWbName = "Book1.xls" 'Workbook X
SourceWsName = "Sheet1" 'Spreadsheet1 in Workbook X
DestWsName = "Sheet1" 'Spreadsheet1 in Workbook Y

With Application

.Workbooks.Open ThisPath & "\" & SourceWbName

.Workbooks(SourceWbName).Worksheets(SourceWsName).Rows(2).Copy

Set DestWs = ThisWorkbook.Worksheets(DestWsName)

DestWs.Activate

Set DestRow = DestWs.Cells(1, 1).CurrentRegion

Set DestRow = DestRow.Resize(1, 1).Offset(DestRow.Rows.Count)

DestRow.Select

ActiveSheet.Paste

.CutCopyMode = False

.Workbooks(SourceWbName).Close SaveChanges:=False

End With
End Sub

This macro ("DoIt") needs to be placed in workbook Y. When the macro runs, it opens "book1.xls" (your workbook x), copies row 2 from "sheet1", pastes it to the next blank row of the "sheet1" of the workbook and then closes "book1". It assumes both workbooks are in the same folder.

bigup, it this is what you basically want to do (give it a try by changing the names of the workbooks and worksheets as shown by the comments), then it can be tweeked to display a dialog box for you to select the "source spreadsheet" and then automatically copy row 2 from the spreadsheet you select. I've also been lazy and not commented anything (although VB is relative easy to get tge gist) - again if this is what you want, and nobody comes up with a better way, then I'll comment it and provide more guidance on setting up the macro. One question - what version of excel are you using?

bigup

thanks guys for the replies and Lucian for the code.

Im using excel 2007. Yes both workbooks have already been created and will be open. I receive a vast amount of new spreadsheets via email, which have only one line of data on them and one header line. The line of data (excluding header) has to be copied from one excel file and pasted into my main excel file on the next blank row.

I will play around with your macro lucian and see how it goes. :beer:
Thanks for taking the time to reply everyone.

LucianH

OK, so it's pee'd it down this afternoon and I got bored - so I had another play. What started out as a simple bit of code expands slightly when you add in the get filename. And expands even more when done properly (ie commented)!. Good fun though and keeps the old grey cells exercised!:

Option Explicit

Sub DoIt()
'This macro resides in you Workbook Y (the "destination" workbook)

Dim FoundWs As Boolean 'flag used to check if opened workbook is OK

'variables used for the Source workbook details
Dim SourceWb As Workbook
Dim SourceWs As Worksheet
Dim SourceWbName As Variant
Dim SourceWsName As String

'Variables used to the destination workbook details
Dim DestWs As Worksheet
Dim DestWsName As String
Dim DestRow As Range


'

---

'Variables you need to set up
SourceWsName = "Workheet1" 'Spreadsheet1 in Workbook X
DestWsName = "Workheet1" 'Spreadsheet1 in Workbook Y


'

---

'sets the range DestRow to the first cell on the first empty row
'of the DestWsName worksheet in this workbook

'set up DestWs as DestWsName worksheet in this workbook
Set DestWs = ThisWorkbook.Worksheets(DestWsName)

'Activate it (ie, select it)
DestWs.Activate

'Set the range DestRow to the current region. This is effectively
'the range of cells that hase data in them
Set DestRow = DestWs.Cells(1, 1).CurrentRegion

'resise the range DesrRow to a single cell and "reposition" it
'at the start of the first blank row (using offset)
Set DestRow = DestRow.Resize(1, 1).Offset(DestRow.Rows.Count)


'

---

'Now opens the source workbook, checks if it has a worksheet of the
'right name, copies row 2 to the clipboard, pastes it to this workbook
'and closes the source workbook

With Application

'open a get file dialog box
SourceWbName = .GetOpenFilename("Excel workbook, *.xl*", , "Select File with Row to Import")

'if it's not false then the user has selected a file
If SourceWbName <> False Then

'open the workbook (and it will become the active workbook)
.Workbooks.Open SourceWbName

'set SourceWb to the just-opned file
Set SourceWb = ActiveWorkbook

'set a flag for use in checking of worksheet is in workbook
FoundWs = False

'Check that the workbook contains the worksheet with the right name
For Each SourceWs In SourceWb.Worksheets

'if the workseet exist then set flag and exit for loop
If SourceWs.Name = SourceWsName Then
FoundWs = True
Exit For
End If

Next

If FoundWs Then 'worksheet exists so do the copy

'from the "for each" loop above, SourceWs has been set to the
'worksheet which has the row which is to be copied so
'copy row 2 to the clipboard
SourceWs.Rows(2).Copy

'select the destination worksheet
DestWs.Activate

'and select the cell at the beginning of the first blank row
'as set in the routine above
DestRow.Select

'and paste the row from the SourceWs
ActiveSheet.Paste

'clear the clipboard
.CutCopyMode = False

'and close the source workbook without saving
SourceWb.Close SaveChanges:=False

Else 'tell user that worksheet doesn't exist

'close the source workbook without saving
SourceWb.Close SaveChanges:=False

MsgBox SourceWsName & " does not exist in the file you've selected", vbExclamation, "File Open Error"

End If

End If

End With

End Sub


(Unfortunately when cut pasted into this forum it looses all the indents so it's not so easy to read.)

When you run the macro, select the file with the row you want to import and it will open it, copy it in and then close it.

sarahlaurie100

victor2 wrote: »

Are you consolidating the data from many spreadsheets into one? If so, and these spreadsheets already exist, as against being newly created each time, then a simple formula can reference other spreadsheets/workbooks.

Perhaps if you outline what you are trying to achieve i a little more detail, there's a relatively easy way to achieve the end result without the need for macros.

I would favour this approach and avoid using macros when possible to avoid compatiblity issues with future versions of MS Office

LucianH

sarahlaurie100 wrote: »

I would favour this approach and avoid using macros when possible to avoid compatiblity issues with future versions of MS Office

There's absolutely nothing wrong with using macros for doing repetative tasks. For simple macros not calling obscure APIs then the risk is low. The extra productivity that you can get with macros can far outweigh the extra time fixing simple macros if there is a compatibility issue.

And, in this instance where:

bigup wrote: »

I receive a vast amount of new spreadsheets via email, which have only one line of data on them and one header line. The line of data (excluding header) has to be copied from one excel file and pasted into my main excel file on the next blank row.

... I can't see how you can do it by "simply" referencing the files

LucianH

bigup wrote: »

I receive a vast amount of new spreadsheets via email, which have only one line of data on them and one header line. The line of data (excluding header) has to be copied from one excel file and pasted into my main excel file on the next blank row.

Depending on how you handle the attachements to the email, a better way of doing this could be to have the macro as part of your excel default template and change it so that it opens the "master" spreadsheet and copies the data. If you attach the macro to a toolbar button you only need to open the spreadsheets from the emails (ie, double clicking on it) and then click the button to copy the data. This will save you having to save each file from the emails, and then using the macro to import the data from the file.