daily pastebin goal
33%
SHARE
TWEET

Untitled

a guest Feb 20th, 2019 80 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub Sheet2_Button1_Click()
  2. '
  3. ' Sheet2_Button1_Click Macro
  4. '
  5. Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range
  6.  
  7. 'If an error occurs skip code to the Err-Hanlder line and the display the error message.
  8. On Error GoTo Err_Handler
  9.  
  10. 'This is the sheet where your copy information from. Change "Sheet1" to the name of your soure sheet
  11. Set SourceSht = ThisWorkbook.Sheets("Sheet1")
  12.  
  13. 'Name of the sheet where data is to be copied to. Rename Sheet2 to the name of your target sheet
  14. Set TargetSht = ThisWorkbook.Sheets("Sheet2")
  15.  
  16. 'This is the cells you will copy data from. This is targeting cells B1 to the last used cell in column B
  17. Set SourceCells = SourceSht.Range("c3:c0" & SourceSht.Range("c4").End(xlUp).Row)
  18.  
  19. 'This is finding the next column available in the target sheet. It assumes dates will be in row 1 and data in row 2 down
  20. If TargetSht.Range("A1").Value = "" Then
  21.     'Cell A1 is blank so the column to put data in will be column #1 (ie A)
  22.     SourceCol = 1
  23. ElseIf TargetSht.Range("IV1").Value <> "" Then
  24.     'Cell IV1 has something in it so we have reached the maximum number of columns we can use in this sheet.
  25.     'Dont paste the data but advise the user.
  26.     MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
  27.     'stop the macro at this point
  28.     Exit Sub
  29. Else
  30.     'cell A1 does have data and we havent reached the last column yet so find the next available column
  31.     SourceCol = TargetSht.Range("IV1").End(xlToLeft).Column + 1
  32. End If
  33. 'Put in the date in the appropriate column in row 1 of the target sheet
  34. TargetSht.Cells(1, SourceCol).Value = Format(Date, "DD/MM/YYYY")
  35. 'We can now start copying data. This will copy the cells in column B from the source sheet to row 2+ in the target sheet
  36. SourceCells.Copy TargetSht.Cells(2, SourceCol)
  37.  
  38. Exit Sub 'This is to stop the procedure so we dont display the error message every time.
  39. Err_Handler:
  40. MsgBox "The following error occured:" & vbLf & "Error #: " & Err.Number & vbLf & "Description: " & Err.Description, _
  41.         vbCritical, "An Error Has Occured", Err.HelpFile, Err.HelpContext
  42.  
  43.  
  44. '
  45. End Sub
  46.    
  47. Sub Button3_Click()
  48. '
  49. ' Button3_Click Macro
  50. '
  51.  
  52.  
  53. Range("C3").Select
  54. Selection.Copy
  55.  
  56. Range("J10").Select
  57. Selection.Insert Shift:=xlDown
  58.  
  59. Application.CutCopyMode = False
  60.  
  61.  
  62.  
  63. '
  64. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top