Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 11th 09, 04:24 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2005
Posts: 8
Default Macro - Copy/Paste Special to Next Blank Line

I have a macro that I'm using to copy and paste special into another spreadsheet...the problem I am having is that it will not select the next blank line in the spreadsheet, it overlays what I already have. I need to have it keep adding to the existing data and I'm not sure how to do it. I'm sure it is something simple but I'm new to all of this!

Here is my existing code:
Sub Load_Data()
'
' Keyboard Shortcut: Ctrl+z
'
Workbooks.Open Filename:="F:\SSCW\Load File.xls", UpdateLinks:=0
Windows("TEST- XXXXXX.xls").Activate
Sheets("Sheet1").Select
Range("A2:H2").Select
Selection.Copy
Windows("Load File.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

  #2   Report Post  
Old February 11th 09, 09:45 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,123
Default Macro - Copy/Paste Special to Next Blank Line

Hi lajohn63

See the example on this page
http://www.rondebruin.nl/copy1.htm

Try this example
What if the Database sheet is in another workbook





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"lajohn63" wrote in message ...

I have a macro that I'm using to copy and paste special into another
spreadsheet...the problem I am having is that it will not select the
next blank line in the spreadsheet, it overlays what I already have. I
need to have it keep adding to the existing data and I'm not sure how to
do it. I'm sure it is something simple but I'm new to all of this!

Here is my existing code:
Sub Load_Data()
'
' Keyboard Shortcut: Ctrl+z
'
Workbooks.Open Filename:="F:\SSCW\Load File.xls", UpdateLinks:=0
Windows("TEST- XXXXXX.xls").Activate
Sheets("Sheet1").Select
Range("A2:H2").Select
Selection.Copy
Windows("Load File.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub




--
lajohn63

  #3   Report Post  
Old August 27th 12, 09:27 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 1
Send a message via Skype™ to Andrew Celentano
Default

Quote:
Originally Posted by Ron de Bruin View Post
Hi lajohn63

See the example on this page
http://www.rondebruin.nl/copy1.htm

Try this example
What if the Database sheet is in another workbook





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"lajohn63" wrote in message ...

I have a macro that I'm using to copy and paste special into another
spreadsheet...the problem I am having is that it will not select the
next blank line in the spreadsheet, it overlays what I already have. I
need to have it keep adding to the existing data and I'm not sure how to
do it. I'm sure it is something simple but I'm new to all of this!

Here is my existing code:
Sub Load_Data()
'
' Keyboard Shortcut: Ctrl+z
'
Workbooks.Open Filename:="F:\SSCW\Load File.xls", UpdateLinks:=0
Windows("TEST- XXXXXX.xls").Activate
Sheets("Sheet1").Select
Range("A2:H2").Select
Selection.Copy
Windows("Load File.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub




--
lajohn63


EXCEL UPDATE:

You can now select Under the Macros Tab "Use Relative Addresses"
With this on, build your macro again and you will find that it will work


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro for Edit/Copy/Paste Special icanlearn Excel Worksheet Functions 4 July 17th 08 06:54 PM
Copy range in macro using paste special values Jeff Excel Discussion (Misc queries) 2 August 20th 07 08:12 PM
how to create a copy/paste special/transpose macro? [email protected] Excel Discussion (Misc queries) 6 August 8th 07 06:07 AM
Copy & Paste Special Macro Secret Squirrel Excel Discussion (Misc queries) 3 January 27th 07 03:15 AM
Copy Paste Special Macro Bud Hughes Excel Discussion (Misc queries) 2 August 31st 05 02:00 AM


All times are GMT +1. The time now is 12:29 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017