I created a lookup formula so I can transfer coulmn data from sheet 1 to sheet 2 of my excel workbook. But since it's a shared workbook, sometimes formulas got deleted. I was thinking to do a VBA program for it so that we can just run the program everytime we want to copy data from one sheet to another without creating cell formula anymore.
ex.
from sheet 1 copy A1:A100 and C1:C100 to Sheet 3 B1:B100 and C1:C100
from sheet 2 copy B1:B100 and D1:100 to Sheet 3 A1:A100 and D1:100
Thanks!
Last edited by genracela; 04-11-2010 at 10:06 PM.
Hi,
You can record the sequence and use it.
like this
[VBA]
Sub CopyTo()
Sheets("Sheet1").Select
Range("A1:A100").Select
Selection.Copy
Sheets("Sheet3").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
[/VBA]
it is simple starting method to
With Regards,
Maayan
... and having recorded it, then try to strip out the verbiage. You can then add the other ranges in the same manner
[VBA]Sub CopyTo()
Sheets("Sheet1").Range("A1:A100").Copy
Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Great! I'll start the program rightaway!
Thanks!
quick question... what if data would come from a different workbook not just sheet?
workbook("Sheet1").Select ?
No need to select. If the book is open then
[VBA]
Sub CopyTo()
Workbooks("Test.xls").Sheets("Sheet1").Range("A1:A100").Copy
Thisworkbook.Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Keep both the workbook open... and run the following macro
[VBA]
Sub CopyTo()
Workbooks("From").Sheets("Sheet1").Range("A1:A100").Copy
Workbooks("To").Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub
[/VBA]
Thanks!
Another question, what if I want to start pasting on the blank row?
This doesn't mean anything.Originally Posted by genracela
You can change the target to the ActiveCell or otherwise identify using Cells(Rows.Count,1).End(xlUp)(2) or similar.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
yes, i got it.
I added
[VBA]
ThisWorkbook.Sheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
Workbooks("Excel Help.xls").Sheets("Shortcut keys").Range("A1:A100").Value
ThisWorkbook.Sheets("Sheet3").Columns("B").AutoFit
[/VBA]
thanks!!!
Posting Permissions
Reply With Quote