Macro: 2 workbooks, cut & paste with out overwriting data

[Jbmcd]

I am new to macros, I need some help as to how do I add code to a working macro to accomplish an additional function.


What I have is workbook1 SalesTracker, 15 rows in column A and B the data is cut then pasted to workbook2 sheet2 by clicking a button. My code for this is working fine. The problem is every time I run the macro it overwrites the previous data. I need to be able to add to the previous data in workbook2 Sheet2. I have found code that is supposed to look for blank cells in the column that the data is in and paste the new data in blank cells.


I’m not sure where or what mods I need to make to the code to get two different macros running as one macro, or een i is i posible. Can anyone point me in the right direction?


Here is the code I’m using that works fine that I have in Module2:



Sub CutPaste()
'
' CutPaste Macro
'

'
Range("A1:B14").Select
Selection.Copy
Windows("TrackerReports.xlsm").Activate
Range("A2:B15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("Tracker.xlsm").Activate
Range("A2:A13").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A2").Select
End Sub

Also this code is tied to the above code in Module1:

Private Sub CommandButton1_Click()

Dim LastRw As Long

LastRw = Sheets("Sheet2").UsedRange.Rows.Count

Sheets("Sheet2").Cells(LastRw + 1, "A").Value = Sheets("Sheet1").TextBoxes("TextBox 1").Text
Sheets("Sheet2").Cells(LastRw + 1, "B").Value = Sheets("Sheet1").TextBoxes("TextBox 2").Text
Sheets("Sheet2").Cells(LastRw + 1, "C").Value = TimeValue(Now)
End Sub

This is the code I’m trying to tie in, but not sure where of how to mod the code above to tie it in:

Option Explicit

Sub saleschartnew()
'
' saleschartnew Macro
' Macro recorded 19/04/2007 by Network Services
Dim wsTo As Worksheet
Dim wsfrom As Worksheet
Dim rnextCl As Range
Set wsfrom = Sheets("Entry form")
Set wsTo = Sheets("Monthly Sales Chart")
Set rnextCl = wsTo.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0)


rnextCl.Value = Range("G3")
rnextCl.Offset(0, 1).Value = Range("C13")
rnextCl.Offset(0, 2).Value = Range("E20").Value
Sheets("Entry form").Select

End Sub

 

[farmerscott]

Your question is a little confuding so....

1. you have 3 codes- Sub CutPaste(), Private Sub CommandButton1_Click() and Sub saleschartnew(): can you explain what each does or you are trying to do with it.
2. can you explain further what you what the new code to do. For example I want the code to do A, then do B, and so on.
3. make sure you include the name of all workbooks and worksheets that are relvant to the exercise.

thanks

FarmerScott

 

[Jbmcd]

Hi, basically I have a daily sales tracker that is filled out many times per day with different account numbers and different products sold to each account. What I need to do is save each accounts sales (products and product codes) to a 2nd woorkbook that is organized in columns, different columns for each day of the month. Actually 2 columns per day one for product codes and the 2nd for product names for each day of the month. There are many sales each day to different accounts. The 2nd work book is where I need to store all of the different account sales with out overwriting any of them.

Sub CutPaste(), cuts the account number and each of the sales to that account from “Tracker.xlsm /Tracker”, then pasts the data into “TrackerReports.xlsm/Sheet2” with a click of a button (the Private Sub CommandButton1_Click() ). This part of the code is working fine how ever each time I click the button activating the macro it overwrites all the previous data from the previous sale in “TrackerReports.xlsm/Sheet2” .

the next code Sub saleschartnew(): is supposed to stop overwriting of account data that is being pasted into “TrackerReports.xlsm/Sheet2” by shifting down the columns to the next available empty cells, then paste the data so that nothing is overwritten. This is the part I’m having a very hard time with.

I’d like to be able to accomplish all of this with just one click of the button in “Tracker.xlsm /Tracker”.

I’ve searched and tried many different codes that are supposed to find the next empty cell in a column and paste data into the empty cells, but so far no luck at all getting it to work. As I said I’m new to macros and not sure if I can run three or more macros off the click of one button and how to lay out the code. Separate sheets, combine the code, add entire code below in the same sheet? Any help or direction you can give me is greatly appreciated!