Macro Help - Copy from One Worksheet to Another

FM1

Board Regular
Joined
Jan 1, 2008
Messages
62
Office Version
  1. 365
Hi everyone. I have a workbook with two worksheets - Catalogue Request Summary (CRS) and Presentation Server Types (PST). The PST page is basically a form with validated entry fields, hence the information is updated constantly. The CRS is a summary tablised version of the information in the PST page.

I have setup a macro which copies across the information from the PST sheet and pastes into the CRS sheet. The problem is that when the macro is run, it over-writes the information instead of pasting the new data into the next row.

So basically I need the macro so that will copy any new information to a new row in the CRS page whenever new information has been added to the PST page the the button clicked.

I know it would be a lot easier if there was an attachment but there doesnt seem to be the option. Please pm your email address so I can send it through.

Thanks in advance.

Edit - I have uploaded the file to an online storage site if anyone wants to download it. Link:http://www.4shared.com/file/91957046/17f514da/Catalogue_Test_a.html
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

FM1

Board Regular
Joined
Jan 1, 2008
Messages
62
Office Version
  1. 365
Please post the macro code that you have.

The problem with the macro code is that its all recorded so there is a fair bit of noise in the whole thing. Im not good enough with macros to be able to get rid of it all.

Shall I still post the code?

Also, I have uploaded the file on an online file sharing facility so feel free to download it if you need to have a look the file. Should make it a lot easier.
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Is it this macro?

Code:
Sub CopyBlock()
  'Copies Sheet1 range with upper-left corner cell B4 to Bn where n
  'is last data-filled row
      Range("E9:F33").Select
    Selection.Copy
    Sheets("Catalogue Request Summary").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("K51").Select
    Sheets("Presentation Server Types").Select
End Sub
 
Upvote 0

FM1

Board Regular
Joined
Jan 1, 2008
Messages
62
Office Version
  1. 365
Yep. Thats the one.

Edit - Add and Add2 was me just experimenting. So far CopyBlock seems to work the best.
 
Last edited:
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub CopyBlock()
Dim LR As Long
'Copies Sheet1 range with upper-left corner cell B4 to Bn where n
'is last data-filled row
LR = Sheets("Catalogue Request Summary").Range("C" & Rows.Count).End(xlUp).Row
Sheets("Presentation Server Types").Range("E9:F33").Copy
Sheets("Catalogue Request Summary").Range("C" & LR + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub
 
Upvote 0

FM1

Board Regular
Joined
Jan 1, 2008
Messages
62
Office Version
  1. 365
That has worked perfectly. Thanks very much for your help VoG. It is greatly appreciated.
 
Upvote 0

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this

Code:
Sub Add()
'
' Add Macro
' Macro recorded 09/03/2009 by MBI85
'
Dim LR As Long
'
    Range("E9:F33").Copy
    Sheets("Catalogue Request Summary").Select
    LR = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row + 1
    Cells(LR, 3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Presentation Server Types").Select
    Range("E9:F9").Select
End Sub

edit: to slow VoG already beat me, but here are my efforts anyway
 
Upvote 0

FM1

Board Regular
Joined
Jan 1, 2008
Messages
62
Office Version
  1. 365
try this

edit: to slow VoG already beat me, but here are my efforts anyway

Thanks for the effort texasalynn. Appreciate the effort. :)

also wanted to compliment you on the worksheet/form. Very nicely done
Thanks for that. Always nice to hear some positive feedback.

NB - I just realised I left some sensitive data on the file so I have removed it.
 
Upvote 0

Forum statistics

Threads
1,191,095
Messages
5,984,637
Members
439,898
Latest member
Zack0611

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top