Push Data to Another Workbook Tab

[EldrckTW]

I've automated as much as I can, but I am now at a road block. Any help would be great!.

Simply put,

I have two worksheets in my "My Documents" folder on my C:\ drive. Let's call one "Worksheet 1" and the other "Worksheet 2". "Worksheet 1" has data in the first tab in cells A1, B1 and C1.

I would like to have a button on "Worksheet 1" that will take the data in cells A1, B1 and C1 and push them to a specific tab in "Worksheet 2". Also, the data must be added to the first row that does not have data present. Ex. so if I have already pushed data to "Workbook 2" a few times, data added the next time will be one row below the last.

Any help would be GREATLY appreciated!!

 

[John Davis]

I've automated as much as I can, but I am now at a road block. Any help would be great!.

Simply put,

I have two worksheets in my "My Documents" folder on my C:\ drive. Let's call one "Worksheet 1" and the other "Worksheet 2". "Worksheet 1" has data in the first tab in cells A1, B1 and C1.

I would like to have a button on "Worksheet 1" that will take the data in cells A1, B1 and C1 and push them to a specific tab in "Worksheet 2". Also, the data must be added to the first row that does not have data present. Ex. so if I have already pushed data to "Workbook 2" a few times, data added the next time will be one row below the last.

Any help would be GREATLY appreciated!!

Maybe something like this?

Sub PushData()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Windows("Workbook1.xls").Activate
Sheets("Sheet1").Range("A1:C1").Cut
Windows("Workbook2.xls").Activate
Sheets("Sheet1").Range("A" & LR + 1).Insert shift:=(xlDown)
Windows("Workbook1.xls").Activate
Sheets("Sheet1").Range("A1:C1").delete shift:=(xlLeft)

End Sub

 

[EldrckTW]

After doing more research, I actually figured it out on my own and made a couple enhancements:


'Starting with Worksheet One Open
Sheets("Name Of Tab In Worksheet 1").Select
'Select and Copy data
ActiveSheet.Rows("1:1").Select
Selection.Copy
'Jump to Workbook 2
Windows("Worksheet 2.xls").Activate
Sheets("Name Of Tab In Worksheet 2").Select
'Select where new information is going to be pasted
ActiveSheet.Rows("1:1").Select
'Shift Cells down and Insert new data
Selection.Insert Shift:=xlDown
'Formulas were in A1, B1, C1...so now need to paste only values to get rid of formulas.
ActiveSheet.Rows("1:1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Return to Worksheet 1
Windows("Worksheet 1.xls").Activate



*The only caveat is that I must have both sheets open at the same time for this to work. I would have liked to have one closed, but I don't think that is possible.

The enhancements were instead of adding new entries at the bottom of Worksheet 2, just insert the new items at the top. Thanks for your help and it looks like what yo urecommended would have basically done the job.

 

[John Davis]

After doing more research, I actually figured it out on my own and made a couple enhancements:


'Starting with Worksheet One Open
Sheets("Name Of Tab In Worksheet 1").Select
'Select and Copy data
ActiveSheet.Rows("1:1").Select
Selection.Copy
'Jump to Workbook 2
Windows("Worksheet 2.xls").Activate
Sheets("Name Of Tab In Worksheet 2").Select
'Select where new information is going to be pasted
ActiveSheet.Rows("1:1").Select
'Shift Cells down and Insert new data
Selection.Insert Shift:=xlDown
'Formulas were in A1, B1, C1...so now need to paste only values to get rid of formulas.
ActiveSheet.Rows("1:1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Return to Worksheet 1
Windows("Worksheet 1.xls").Activate



*The only caveat is that I must have both sheets open at the same time for this to work. I would have liked to have one closed, but I don't think that is possible.

The enhancements were instead of adding new entries at the bottom of Worksheet 2, just insert the new items at the top. Thanks for your help and it looks like what yo urecommended would have basically done the job.

Just out of curiousity? Is there any special reason you're naming your Workbooks Worksheet1 and 2? It seems to confuse matters.

 

[EldrckTW]

I named them one and two the try and keep it simple for the forum. I named them differently when I am using them.