Macro problem - copying specific cell's from one sheet to another

**Mrgud** · 02-11-2012, 03:59 PM

Hello everybody
First, I would like to thank some people on this forum for their posts. Even I (and I don’t usually use excel that much) manage to solve some problems by myself (print button, save as button, locking and unlocking cell’s... etc)

. But now I’m stuck and I really need your help. I’m working on some document for my company (complains report), and I’m trying to automate the whole procedure.
Here is the problem: I have a workbook with two sheets. Sheet1 is a document where the user can type in the various data in unlocked cell’s, everything else is locked. After data input I’ve planned for user to have three buttons 1. Print button (PRINTAJ) – this button prints active worksheet (sheet1), and it works 2. Save as button (SPREMI ) – this button saves sheet1 to a specific folder (for now it is desktop) generating the name from two specific cell values, and it also works 3. Transfer data button (PREBACI PODATKE) – this button doesn’t work at all, and here I need your help.

1. After inserting all necessary data in sheet1 user should click first on PRINTAJ, and print the document - working
2. After they should click SPREMI, or save as button and save this file – working
3. Third click is on PREBACI PODATKE, or data transfer button. With this click users should transfer specific (not all) unlocked fields from sheet1 into sheet2 and inform the user with a message that the “data was successfully copied”. After clicking OK on the message sheet1 should be empty and ready for user to type in new data in cell’s.
4. Here is how the cell’s should be copied:

Sheet1 Sheet2
G7:I7 A4
H4 B4
I4 C4
J4 D4
F10:I10 E4
E13:I13 F4
E28:I28 G4
F36 H4
H36:I36 I4
G44 J4
E47:I47 K4

When the procedure is repeated, next data from sheet1 should be copied in next free row (sheet2 from A5 to K5)... and so on, you get the point

In my Macro1 I've tried with just three fields (H4, I4, J4)... any help would be greatly appretiated.
Thank you in advance
Mrgud

TEST.xls

**MaczaQ** · 02-11-2012, 04:24 PM

Hi Mrgud,

I think you will be able to move forward with your issue yourself

you need only 2 things
1) check first empty row in sheet2
2) match source ranges from sheet1 and write values into sheet2 for new row

so try to use something like this - of course complete below code with your ranges:

Sub copyData()
 firstRow = Sheet2.columns(1).Find("*", searchdirection:=xlprevious).row + 1
 sheet2.range("A" & firstRow).value = sheet1.range("g7").value
 sheet2.range("B" & firstRow).value = sheet1.range("h4").value
'...
'... and so on
'...
 sheet2.range("K" & firstRow).value = sheet1.range("E47").value

msgbox “data was successfully copied”
End Sub

Please think about it - I writting reply directly online without testing in Excel

**Mrgud** · 02-11-2012, 04:32 PM

Hi maczaq
Thank you for your fast reply... I will try it and let you know the result.
Bye

**Mrgud** · 02-12-2012, 03:10 AM

Good morning MaczaQ
I've tried, but there is some error report... and I don't know why?! I think in first line of code ("FirstRow...)
I'm not into this programming thing, but I like the challenges... for these first two button I've find the answer with a little bit of luck, a lot of reading and using a method of trial and errors

But this third button is not a challenge to me any more, it's a nightmare.
Thank you for trying
Kind regards
Davor

**Mrgud** · 02-12-2012, 09:32 AM

This is what I want... but I want to apply this probaMacro1.xls to TEST.xls
Problem is that in probaMacro1 all date is in the same row and in TEST.xls they arent.probaMacro1.xls
Please help.
Mrgud

**MaczaQ** · 02-12-2012, 02:27 PM

Hello

In probaMakro1 you have not marged cells so you can simpy copy and PastSpecial with transpose data, but in your main workbook "TEST.xls" you had few marged cells (i.e. F10:I10, E13:I13, E28:I28) so copy and pasteSpecial with transpose will return error because source date have other structure then destination range.

I update your file TEST.xls with my idea - I hope You will enjoy it.

'this have to be before any procedure - so mainly above the whole code
Option Base 1

'and here is procedure for copy data
Private Sub CommandButton3_Click()
Set sSH = Sheets("Sheet1") 'set source sheet
Set oSH = Sheets("Sheet2") 'set output sheet
CellsToCopy = Array("g7", "h4", "i4", "j4", "f10", "e13", "e28", "f36", "h36", "g44", "e47")

 firstRow = oSH.Cells.Find("*", searchdirection:=xlPrevious).Row + 1
   'copy all necessary data
   For i = 1 To UBound(CellsToCopy)
    oSH.Cells(firstRow, i).Value = sSH.Range(CellsToCopy(i)).Value
   Next i

MsgBox "data was successfully copied"
End Sub

in attached file I update working example - I hope it is what you need