VBA help! copy and paste to database sheet

[Emily2]

Hello!

Wondering if someone can help me! I have a spreadsheet that i use as a form for people to fill in and has a command button to submit. When i receive the submitted spreadsheet i need to be able to click another command button that transfers the value of a range of different cells, into my database sheet but as one row.

I have managed to record something, however, if one of the cells does not have a value in it, the next time i request the copy and paste the macro is pasting the data into the blank cell on the row above?

here is what i have so far

Workbooks.Open FileName:= _
"\\J:\sharedata\Finance\Financial Controller\Project Costing\Costing models\Costing Database.xlsx"
Windows("database test.xlsm").Activate
Range("K3:P3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("database test.xlsm").Activate
Range("C7:E7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("database test.xlsm").Activate
Range("G7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("database test.xlsm").Activate
Range("K7:P7").Select
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Is anyone able to help me please?

Thank you in advance

E

[Yongle]

It would be easier if you could post a copy of the original sheet (database test )and what the final sheet should look like (Costing database).

If confidentiality is an issue just replace those values with something like "XXX"
please use a different replacement for each range that you are copying and pasting to avoid confusion

Will the number of entries vary each time you run the macro?

[Emily2]

Thank you Yongle,

this is the Costing database spreadsheet columns



Index Number
PI
Activity
Activity Category
Faculty
Department
Activity Centre
DI Costs Existing
DI Costs New
DI Cost Non UCS
Non Pay DI Costs
Total DI costs
UCS staff DA costs
Estates Costs
Indirect costs
Total DA costs
Full cost
Turnover
fEC recovery
Project +/-
Total Fin contribution
Cont as % price
Contribution to Centre
Contribution to dept
Split Faculty
Split Activity Centre
Split Percentage
Total
Split Faculty
Split Activity Centre
Split Percentage
Total
Split Faculty
Split Activity Centre
Split Percentage
Total
Project title
Project funder name
Funder Category
Project start date
Project end date
Submission deadline
Decision date
Consultancy payment to employee
Total Backfill
UCS Project team
Faculty
Department
UCS Project team
Faculty
Department
UCS Project team
Faculty
Department
Joint application?
Details
Organisation lead
Ucs named collaborator?
Local/National/International
IP generation
UCS staff access
Costing submitted by
Date
VAT code
Invoice total exc VAT
VAT
Total invoice




the database test sheet has cells for each of these columns, which i can do a macro to bring these across, but, my problem is, when there is a blank one, for example no DI costs existing, when i go to move another database test sheet over, it pastes the DI costs existing into the first line as its blank, not in the same line as everything else.

Ultimately this is a costing model that different people fill in, and would like their data stored on the costing database. I would like the macro to copy all of my required cells on the database test, and paste these to a new row each time

many thanks

E

[Yongle]

Hi Emily
By recording a macro you get a copy of what worked at the time. You always must go back and look over what the macro has written and see if it will make sense every time. Sometimes you get lucky, usually you do not!
There may be some other issues with your ranges - they are all fixed. I suggest you check that your "copy from"ranges do not need to flex with your input. If they do, then we can address that later.

Problem
The problem you have noticed happens because the next row is determined by finding the last entry in a particular column and "blanks" are ignored. Because the Row.count is being executed against each column separately, then the starting point for the "paste" will vary by column depending on whether or not there was a blank cell in the previous row in each column.
Fix
Usefully we can combine "UsedRange" with Rows.Count (instead of an individual column) and this looks at your existing datatable and for the last entry in any column to determine the last row, and from that we can determine the next row for pasting. (just be aware that you cannot use this if your datatable has blank rows or columns )
To use this several times in the code, you have to use a variable and tell the macro how to calculate its value. I have named that variable NextRow and its value is the row count for your datatable + 1
Hopefully the instructions below are self explanatory. Suggest you try this out on a COPY of your workbook.
Please ask for help if they are not.
If this solves you problem, can you come back and close the thead - option is under Thread Tools
thanks
Yon




'Put this at the top of the code below the line Windows("database test.xlsm").Activate

Dim NextRow As Long
NextRow = ActiveSheet.UsedRange.Rows.Count + 1
MsgBox "Next Row is : " & NextRow



'DELETE ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 1 and REPLACE WITH
ActiveSheet.Range("A" & NextRow).Select
'DELETE ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row 1 and REPLACE WITH
ActiveSheet.Range("B" & NextRow).Select
'DELETE ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row 1 and REPLACE WITH
ActiveSheet.Range("C" & NextRow).Select
'DELETE ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row 1 and REPLACE WITH
ActiveSheet.Range("D" & NextRow).Select

[Emily2]

Thanks Yongle

I am now getting an error

this is the macro now

Sub Copy_to_database()
'
' Copy_to_database Macro
'


'
Workbooks.Open FileName:= _
"\\xxx\sharedsta\Finance\Financial Controller\Project Costing\Costing models\Costing Database.xlsx"
Windows("database test.xlsm").Activate
Dim NextRow As Long
NextRow = ActiveSheet.UsedRange.Rows.Count + 1
MsgBox "Next Row is : " & NextRow
Range("K3:P3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("database test.xlsm").Activate
Range("C7:E7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("database test.xlsm").Activate
Range("G7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("database test.xlsm").Activate
Range("K7:P7").Select
Selection.Copy
Windows("Costing Database.xlsx").Activate
ActiveSheet.Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub



in the line Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I am getting error run time error 1004 - PasteSpecial method of range class failed?

Thank you for all your help

E

[Emily2]

Hi Yongle

This is now working in some respects, but it is pasting my first cell in row 334, and not copying and pasting any more of the cells that i need?

essentially i would like all of my cells on my form copied, and then pasted to the next row down in my database spreadsheet so that we can then create reports from the Costing database,

thank you for all your help so far

E

[Yongle]

Can you attach the 2 spreadsheet for me, otherwise time consuming to recreate (click on Go Advanced to do that). You may need to zip it.

If confidentiality is an issue just replace those values with something like "XXX"
please use a different replacement for each range that you are copying and pasting to avoid confusion

[Emily2]

1296612967

Hi Yongle

Thanks so much
In the database costing i have highlighted in yellow some of the cells that need copying and pasting to the Costing database names columns, should all be in order as it goes along, If you can just do a few, I can work with your macro from there?

Thanks so Much!

E

[Yongle]

Hi Emily
Please note that I have not changed what your code is doing - I have used a couple of tricks to help you sort it out yourself (best way to learn, I think).
I have amended your code so that you can debug it yourself as you go along.
I am a big advocate of using message boxes when building code, especially if trying something new or complex. VBA does exactly what you tell it, and not what you think you are telling it. So by inserting message boxes along the way, it helps you understand whether what you have actually “told it to do” diverges from what “you think you told it to do”.
In this case you are copying from one place and pasting to another, so the message box tells you how the VBA is interpreting those 2 commands
I noticed that you changed the file name to test 2 but had not amended the code to reflect that – so it was falling over whenever it was trying to “activate” that window
When working with more than one workbook, I declare them at the beginning as variables and then you can use that variable to refer to those workbooks in the rest of the code. That way if you change the file name (as you have done here) you just change the variable once at the beginning of the code. Also it is shorter than typing everything longhand (you recorded the VBA and so that did not apply this time).

Variables which I have added and what they do:
Dim WbD As String, Dim WbC As String – tells VBA what variable types they are
WbD = "database test 2.xlsm" and WbC = "Costing Database.xlsx" - tells VBA exactly what they are
(note that you do not then use “ “ around the name when using variables)

Dim SelectedRange As Range – I declared this variable to use in the message box
Set SelectedRange = Selection - tells VBA to store the selected range

Message Boxes
I number them so that if you notice a problem it is easy to find it in the code
SelectedRange.Address(0, 0) - without the (0,0) the cell ref would look like $A$1

I have attached test 2 including the amended macro – suggest you try running it in my amended file first and then you know you have something that works.
Then amend your file to match and keep amending your original file – this way you can always refer back to a known working starting point (or else save a copy of the macro as it stands now under a different name within your file and do not amend it!)
Any problems please ask
Yon


Sub Copy_to_database()
'
' Copy_to_database Macro
'


'
Workbooks.Open FileName:="\\ucs.ac.uk\dfs\sharedsta\Finance\Financial Controller\Project Costing\Costing models\Costing Database.xlsx"
Windows("database test 2.xlsm").Activate

Dim NextRow As Long

Dim WbD As String, WbC As String
WbD = "database test 2.xlsm"
WbC = "Costing Database.xlsx"

Dim SelectedRange As Range

Windows(WbC).Activate
NextRow = ActiveSheet.UsedRange.Rows.Count + 1
MsgBox "Next Row is : " & NextRow


Windows(WbD).Activate
Range("K3:P3").Select
Set SelectedRange = Selection
Application.CutCopyMode = False
Selection.Copy

Windows(WbC).Activate
ActiveSheet.Range("A" & NextRow).Select
MsgBox " 1 " & vbNewLine & SelectedRange.Address(0, 0) & _
vbNewLine & "will be pasted to range beginning at " & ActiveCell.Address(0, 0)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Windows(WbD).Activate
Range("C7:E7").Select
Set SelectedRange = Selection
Application.CutCopyMode = False
Selection.Copy

Windows(WbC).Activate
ActiveSheet.Range("B" & NextRow).Select
MsgBox " 2 " & vbNewLine & SelectedRange.Address(0, 0) & _
vbNewLine & "will be pasted to range beginning at " & ActiveCell.Address(0, 0)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Windows(WbD).Activate
Range("G7:I7").Select
Set SelectedRange = Selection
Application.CutCopyMode = False
Selection.Copy

Windows(WbC).Activate
ActiveSheet.Range("C" & NextRow).Select
MsgBox " 3 " & vbNewLine & SelectedRange.Address(0, 0) & _
vbNewLine & "will be pasted to range beginning at " & ActiveCell.Address(0, 0)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows(WbD).Activate
Range("K7:P7").Select
Set SelectedRange = Selection
Selection.Copy

Windows(WbC).Activate
ActiveSheet.Range("D" & NextRow).Select
MsgBox " 4 " & vbNewLine & SelectedRange.Address(0, 0) & _
vbNewLine & "will be pasted to range beginning at " & ActiveCell.Address(0, 0)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

[Yongle]

Read this after reading Post#9

Hi Emily - you may groan when you read this post! :crying:


The ranges that you have highlighted in yellow cover several cells, but the values are stored the first cell of each range. So when selecting the range to copy from use K3 (not K3:P3), C7 (not C7:E7) etc


Whilst your mask for collecting the data looks nice for input, you have given yourself a real pain to copy the data into your database.
Take row 24 as an example, you will have to copy and paste the value of each cell separately - why? - because columns D,F,H,J & L are blank. Otherwise you could have copied the entire row as a single range. This applies to most of the form

Solutions
1 If not too late, redesign the form - most of your problems would be eliminated by deleting rows D,F,H,J & L - looking at the form, I do not think it should affect anything - other than looks.
2 Another possibility would be to redesign the Costing Database to allow for all the empty columns - ie add blank columns to the database everywhere required - which is between most of the columns. You will need to keep it like this so that you can keep adding new input. But you could copy the database by macro into a separate sheet after finishing your input each time and a simple macro could eliminate the blank columns.
3 Keep everything as it is and copy and paste each cell separately.

Yon

[Emily2]

Thank you for your help! it works like a dream now!

Emily