Solved: Transfer Data to Another Workbook

[bopo]

Hi everyone
Well I have a bit of a problem, and kind on need some advice/help.
I have a spreadsheet containing data, I have several columns such as quatity, names etc and around 40 records/rows. I have some code which highlights a row If I click on the first cell within the row, however what I want to happen is when a row is highlighted, a button can be clicked which will transfer the data to a new spreadsheet, I have tried using a macro, however obviously this only works for one row, and I need it to work for all rows.
Example
Say row nine is highlight, row A9-G9 will become highlighted, then the data within coulumn A9, should be forwarded to cell G32 B9 to I22 etc etc onto a different spreadsheet, this in the same for all cells highlighted.
The main problem is getting it to work for all rows selected, would appriciate your help on this.
Cheers

[Simon Lloyd]

When you say "Highlighted" do you mean selected or cells coloured?, if its selected then [VBA]Selection.Copy Destination:=YOUR RANGE[/VBA]however, that will not work if you want to copy to individual places

A9, should be forwarded to cell G32 B9 to I22 etc

is this really what you want? split the data to different rows?

[bopo]

Hi

Yeah a mean highlight, such as dragging to cursor over the row, and yeah I do want the data to be copied to different cells within another spreadsheet.

Anyone have any ideas, and thanks for your help Simon.

When you say "Highlighted" do you mean selected or cells coloured?, if its selected then [vba]Selection.Copy Destination:=YOUR RANGE[/vba]however, that will not work if you want to copy to individual places is this really what you want? split the data to different rows?

[mvidas]

Hi bopo,

Without you giving us more information about where you actually want the data copied, all we can do is give you pointers on how you could do it. See the following for an example of copying either the whole A:G range of the row to another sheet, or doing each cell individually from that A:G range:[vba]Sub bopocopy()
Dim SelRG As Range, RW As Range

'sets range variable for columns A:G of any row with a selection on it
Set SelRG = Intersect(Selection.EntireRow, Selection.Worksheet.Columns("A:G"))

For Each RW In SelRG.Rows 'loop through each 'row' (cols A:G) of selection

'copy that "row" to next available row on 'different sheet' in column G
'columns A:G of source sheet will go to G:M of different sheet
RW.Copy Sheets("different sheet").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)

'***** OR *****
'if you want each cell of "row" moved to non-adjacent cells of destination..
With Sheets("different sheet") 'destination sheet
RW.Cells(1).Copy .Range("G32")
RW.Cells(2).Copy .Range("I32")
RW.Cells(3).Copy .Range("M32")
'etc
End With

Next

End Sub[/vba]Matt

[bopo]

Hi

Well I keep getting a subscript out of range error, however I think this is due to my modification of the coding, the copy each cell individually looks great, however I am not sure which pieces of code I need to remove.

Below is the coding changed slightly

* Note Sheet1 contains all the data/rows that need to be copied
*Sheet2 is where each cell within the row should be copied to specific cells

[VBA]Sub ReportSub()
Dim SelRG As Range, RW As Range

'sets range variable for columns A:G of any row with a selection on it
Set SelRG = Intersect(Selection.EntireRow, Selection.Worksheet.Columns("A:K"))

For Each RW In SelRG.Rows 'loop through each 'row' (cols A:G) of selection

'copy that "row" to next available row on 'different sheet' in column G
'columns A:G of source sheet will go to G:M of different sheet
RW.copy Sheets("Sheet2").Cells(Rows.count, "K").End(xlUp).Offset(1, 0)

'***** OR *****
'if you want each cell of "row" moved to non-adjacent cells of destination..
With Sheets("Sheet1") 'destination sheet
RW.Cells(1).copy .Range("G32")
'RW.Cells(2).copy .Range("I32")
'RW.Cells(3).copy .Range("M32")
'etc
End With

Next
End Sub[/VBA]

Thanks for everyones help so far

[mvidas]

Hi bopo,

Telling us the line you get the error usually helps However I see you are wanting to copy to sheet 2, but used both sheet1 and sheet2 in your sub above, so that is likely your problem.

I'd like to help you with specific code for your needs, so I will just ask this directly:

Say you select a cell in row 9 of sheet1, so you will want A9:K9 of sheet1 copied somewhere onto sheet2. Where do you want that copied to exactly?

[lucas]

this should be sheet 2
[VBA]With Sheets("Sheet1") 'destination sheet
[/VBA]

[lucas]

sorry Matt....didn't mean to step on toes...

[mvidas]

No worries, Steve, I don't get offended very easily. Collaboration is key anyways
Besides, if you were then we'd both be stepping on Simon's toes

[lucas]

That's different...he's from down there..


Oops, I thought Simon was an Aussie....

[bopo]

this should be sheet 2
[vba]With Sheets("Sheet1") 'destination sheet
[/vba]

Hi, sorry, it is going to the correct sheet, typo by me, and no row is being highlighted, it just says the error

Hi bopo,

Telling us the line you get the error usually helps However I see you are wanting to copy to sheet 2, but used both sheet1 and sheet2 in your sub above, so that is likely your problem.

I'd like to help you with specific code for your needs, so I will just ask this directly:

Say you select a cell in row 9 of sheet1, so you will want A9:K9 of sheet1 copied somewhere onto sheet2. Where do you want that copied to exactly?

Well it would be something like this

A9 to G23
B9 to D3
C9 to F8

this will be repeated until k9 is reached, and remeber I have code which highlights from A to K when I click on a cell within the A column, as I think its the best method to ensure to data is missed etc.

Sorry, but it is just hard to explain , thanks for your patients.

[mvidas]

You don't need the code that highlights A to K when you click a cell within A.. unless you really want it. My code will work on A:K of whatever rows are highlighted when you run it (my Set SelRG... line).

I can understand the A9->G23, B9->D3 etc, but I guess I was trying to ask one question further. You say you want the macro to run on all selected /highlighted rows. If you have A9:K10 highlighted, do you want A9 first copied into G23 and then A10 also copied into G23? I'm guessing not, which is why I was asking where exactly you want stuff copied into.

But if you'd prefer to manually put the range for each cell within that range (of non-adjacent cells), you can delete my first block within the For/Next loop, since that was intended if you wanted to copy A:K into adjacent cells of the other sheet.

Based on what you've said though, give the following a try:[vba]Sub ReportSub()
Dim SelRG As Range, RW As Range

'sets range variable for columns A:G of any row with a selection on it
Set SelRG = Intersect(Selection.EntireRow, Selection.Worksheet.Columns("A:K"))

For Each RW In SelRG.Rows 'loop through each 'row' (cols A:G) of selection
With Sheets("Sheet2") 'destination sheet
RW.Cells(1).Copy .Range("G23")
RW.Cells(2).Copy .Range("D3")
RW.Cells(3).Copy .Range("F8")
'etc
End With
Next
End Sub[/vba]Matt

[bopo]

That code looks good, however I am getting an error message saying 'cannot change part of a merged cell'

Any ideas on the solution?

[mvidas]

Unmerge the cell?

But seriously, if you can't unmerge the cell for whatever reason (VBA and merged cells don't get along very well), try using this format for pulling the data:[vba]' RW.Cells(1).Copy .Range("G23")
.Range("G23").Value = RW.Cells(1)[/vba]That will put the value of column A from sheet 1 into sheet2!g23. It won't copy any format/formula, it will simply put the value from the source cell into G23
Matt

[lucas]

You solved your own problem...get rid of merged cells

[bopo]

Hi

Yeah, I can get around merged cells, I will just alter the design, just one last question, the new data that appears within Sheet2 inherits the border of the cell, is there a method to not inherit it?