Duplicate Worksheet contents

[murphyz]

I have created a spreadsheet that shows a list of mp3s on my PC and generates a playlist for those that I select 'yes' to uploading.

In order to make this easier for continual updates, I would like to be able to post raw data into one worksheet (taken from Access via the 'analyse with excel' function) and have the main sheet grab information from that. This means I won't have to keep adding formulas everytime I update my mp3 list.

'music' is the main worksheet I have set up
'data' will be the raw data taken from Access.

'data'
The format is as follows (starting in A1 with '>' indicating a new cell):
Artist>Album>Song>Length>Filepath

The above is the header row and the data follows on from that, anywhere from 10 to 5000 songs.

Using a command button I want 'music' to be able to grab the the information starting with the first artist (A2) and ending with the final filepath of the last row.

Basically, it means 'music' will copy all information in the range of the 'data' spreadsheet at the click of the button - and this will work regardless of how much information is in the 'data' sheet.

I can use =COUNTA(A:A) and the same for (1:1) to determine how many rows and columns should be in the range, but have no real idea what the code is for getting this infomation to show in the 'music' sheet.

I was playing around with it last night in an attempt to find out how things worked, and the best I could manage was to take information on the same sheet columns A-C and offset it by three rows (G1 was the COUNTA sum to determine how many rows had content).

Please Login or Register  to view this content.

Although this helped me gain a little knowledge, it was then I realised I am out of my depth a little in trying to get it to work as described above.

Any pointers would be greatly appreciated - thanks.

Mxx

[Nigel_hough]

Hi

This code below will copy the data from data sheet to the music sheet i think this is what you want. I didn't know whether there was any criteria for the songs in sheet to if so an IF statment might me required. If you have any problems let me know

Hope this helps


Sub duplicate()
'select data sheet
Sheets("Data").Select

Range("A2").Select
'loop through all cells
Do While (Selection <> "")

'make music sheet equal to data sheet
Sheets("MUSIC").Range("A" & Selection.Row - 1) = Selection
Sheets("MUSIC").Range("B" & Selection.Row - 1) = Selection.Offset(0, 1)
Sheets("MUSIC").Range("C" & Selection.Row - 1) = Selection.Offset(0, 2)
Sheets("MUSIC").Range("D" & Selection.Row - 1) = Selection.Offset(0, 3)
Sheets("MUSIC").Range("E" & Selection.Row - 1) = Selection.Offset(0, 4)

Selection.Offset(1, 0).Select
Loop

End Sub

[murphyz]

Hiya, thanks - that's definitely what I want.

Just a quick query, is that the code only seems to work if I put it into the 'data' sheet.

Is there a way for me to put it into the 'music' sheet so the data sheet is left button/code free?

Cheers for your help

Mxx

[Nigel_hough]

yea sorry

Where it says

Sheets("Data").Select

it should say : Sheets("Data").Activate

and at the bottom you could put in a

Sheets("Music").Activate (So you are returned to the music sheet)

This way it should run from whereever you start it

[murphyz]

Nice. Thanks - the penny is starting to drop with this stuff now.

One final query, but not something that's important - just a speculation to see if it's possible.

Is it possible to open another spreadsheet to get the data?

i.e. music.xls and data.xls are both saved in the same directory.
Can the code in the music.xls sheet open up the data.xls, grab the info, then close data.xls - or is that too much to ask for?

As I say...this is just for my info, not a great help to my little project.

Thanks again.

Mxx

[Nigel_hough]

You could give this a go i haven't tested it but i think the code should be something like this.

Let me know if it dosen't.



Workbooks.Open Filename:= _
"data.xls"
Range("A2").Select

Do While (Selection <> "")

Workbooks("music.xls").Sheets("MUSIC").Range("A" & Selection.Row - 1) = Selection
Workbooks("music.xls").Sheets("MUSIC").Range("B" & Selection.Row - 1) = Selection.Offset(0, 1)
Workbooks("music.xls").Sheets("MUSIC").Range("C" & Selection.Row - 1) = Selection.Offset(0, 2)
Workbooks("music.xls").Sheets("MUSIC").Range("D" & Selection.Row - 1) = Selection.Offset(0, 3)
Workbooks("music.xls").Sheets("MUSIC").Range("E" & Selection.Row - 1) = Selection.Offset(0, 4)

Selection.Offset(1, 0).Select
Loop

Workbooks("data.xls").Close
Workbooks("music.xls").Activate

[murphyz]

It opens data.xls but then gives me a runtime error saying the range cannot be found.

I tried adding Sheets("sheet1").Activate (also tried Select) after the workbook open function but that didn't help.

Mxx

[Nigel_hough]

Hi

I just gave it a test and it seemed to work.

You need to make sure that the sheet in data is called "data" and the sheet in music is called "music".

Also a good tip if you open the code in vb the click F8 you can step through the code line by line and see where any errors occur.

Here Is the code i tested :-


Workbooks.Open Filename:= _
"data.xls"
Range("A2").Select

Do While (Selection <> "")

Workbooks("music.xls").Sheets("MUSIC").Range("A" & Selection.Row - 1) = Selection
Workbooks("music.xls").Sheets("MUSIC").Range("B" & Selection.Row - 1) = Selection.Offset(0, 1)
Workbooks("music.xls").Sheets("MUSIC").Range("C" & Selection.Row - 1) = Selection.Offset(0, 2)
Workbooks("music.xls").Sheets("MUSIC").Range("D" & Selection.Row - 1) = Selection.Offset(0, 3)
Workbooks("music.xls").Sheets("MUSIC").Range("E" & Selection.Row - 1) = Selection.Offset(0, 4)

Selection.Offset(1, 0).Select
Loop

Workbooks("data.xls").Close
Workbooks("music.xls").Activate

[murphyz]

odd - still no go.

music.xls - sheet name 'music'
data.xls - sheet name 'data'

music.xls has a command button.

code as follows:

Private Sub CommandButton1_Click()
Workbooks.Open Filename:= _
"data.xls"
Range("A2").Select

Do While (Selection <> "")

Workbooks("music.xls").Sheets("MUSIC").Range("A" & Selection.Row - 1) = Selection
Workbooks("music.xls").Sheets("MUSIC").Range("B" & Selection.Row - 1) = Selection.Offset(0, 1)
Workbooks("music.xls").Sheets("MUSIC").Range("C" & Selection.Row - 1) = Selection.Offset(0, 2)
Workbooks("music.xls").Sheets("MUSIC").Range("D" & Selection.Row - 1) = Selection.Offset(0, 3)
Workbooks("music.xls").Sheets("MUSIC").Range("E" & Selection.Row - 1) = Selection.Offset(0, 4)

Selection.Offset(1, 0).Select
Loop

Workbooks("data.xls").Close
Workbooks("music.xls").Activate
End Sub

error occurs at Range("A2").Select

Mxx

[Nigel_hough]

Yea i found the same problem i think this gets rid of it. I also added the screenupdating lines this stops you seeing the different windows open and close.



Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Workbooks.Open Filename:= _
"data.xls"

ActiveWorkbook.Sheets("data").Range("a2").Select


Do While (Selection <> "")

Workbooks("music.xls").Sheets("MUSIC").Range("A" & Selection.Row - 1) = Selection
Workbooks("music.xls").Sheets("MUSIC").Range("B" & Selection.Row - 1) = Selection.Offset(0, 1)
Workbooks("music.xls").Sheets("MUSIC").Range("C" & Selection.Row - 1) = Selection.Offset(0, 2)
Workbooks("music.xls").Sheets("MUSIC").Range("D" & Selection.Row - 1) = Selection.Offset(0, 3)
Workbooks("music.xls").Sheets("MUSIC").Range("E" & Selection.Row - 1) = Selection.Offset(0, 4)

Selection.Offset(1, 0).Select
Loop

Workbooks("data.xls").Close
Workbooks("music.xls").Activate

Application.ScreenUpdating = False

End Sub

[murphyz]

Excellent, that works great.

I've now set it so this code, and other stuff, are under one button on a 'start' sheet.

Clicking the button gets the information I want and unhides the music sheet as well as doing a few much needed formatting things.

So far so good.

Many thanks for your help

Mxx