I need 2 what i`m guessing are simple macros
Part of what I needed was done below but it did not work once i copied the macro into my file (prob my fault?) & it did not fulfil my requirements exactly.
(I`m greatful to the person who did it)
I need the following (Im not managing to record them myself)
Macro 1
Copy cells from sheet Nutri checker to sheet Sheet2, (table below showing what`s to be copied & where). The Dashboard in Nutri Checker is a search. I search the item I want & its details are filled. I want those copied to Sheet2. I might then find another item & also have it`s details copied to sheet2 adjacent to previously copied details. The macro has to handle 1) single items being removed (e.g. I remove screw with all its entries copied). So after removing screw the next item I copy should go into it`s place and 2) the entire thing except for whats written in column A (will always be there) (e.g. I`ll remove torch, screw and everything else copied in) start copying the items in again starting from column B. The cell background for cells B9 & B10 in Sheet2 should be off white.
I`m not sure if this can be handled but if I have copied 3 items and the middle one is removed. If it is too hard to handle I can avoid doing it or re-arrange the cells so as to not break the macro
Macro 2
The sum of each row (irrespective of how many items are added) totalled as shown in the screenshot, with black background, white text and totals written in at the bottom.
*Macro to copy numbers & format table across different sheets
Part of what I needed was done below but it did not work once i copied the macro into my file (prob my fault?) & it did not fulfil my requirements exactly.
(I`m greatful to the person who did it)
I need the following (Im not managing to record them myself)
Macro 1
Copy cells from sheet Nutri checker to sheet Sheet2, (table below showing what`s to be copied & where). The Dashboard in Nutri Checker is a search. I search the item I want & its details are filled. I want those copied to Sheet2. I might then find another item & also have it`s details copied to sheet2 adjacent to previously copied details. The macro has to handle 1) single items being removed (e.g. I remove screw with all its entries copied). So after removing screw the next item I copy should go into it`s place and 2) the entire thing except for whats written in column A (will always be there) (e.g. I`ll remove torch, screw and everything else copied in) start copying the items in again starting from column B. The cell background for cells B9 & B10 in Sheet2 should be off white.
I`m not sure if this can be handled but if I have copied 3 items and the middle one is removed. If it is too hard to handle I can avoid doing it or re-arrange the cells so as to not break the macro
Macro 2
The sum of each row (irrespective of how many items are added) totalled as shown in the screenshot, with black background, white text and totals written in at the bottom.
Sheet: Nutri Checker |
| |
E1 | B5 | |
E2 | B6 | |
E3 | B7 | |
E4 | B8 | |
E5 | B9 | |
A4 | B10 |
VBA Code:
Sub CopyItems()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim NextColumn As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
'last used column
NextColumn = ws1.Cells(5, Columns.Count).End(xlToLeft).Column - 1
'insert column with formatting from the left
If ws1.Cells(5, NextColumn) <> Empty Then
ws1.Columns(NextColumn + 1).Rows("5:10").Insert xlShiftToRight, xlFormatFromLeftOrAbove
NextColumn = NextColumn + 1
End If
'copy values
ws1.Cells(10, NextColumn).Value = ws2.Range("A4").Value
ws1.Cells(9, NextColumn).Value = ws2.Range("E1").Value
ws1.Cells(5, NextColumn).Resize(4, 1).Value = ws2.Range("E2:E5").Value
'total formulas
ws1.Cells(5, NextColumn + 1).Resize(4, 1).Formula = "=SUM(RC2:RC[-1])"
End Sub
*Macro to copy numbers & format table across different sheets