View Full Version : Solved: How to create a VBA program based on lookup formula
genracela
04-11-2010, 09:55 PM
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!:help
MaayanSV
04-12-2010, 07:14 AM
Hi,
You can record the sequence and use it.
like this
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
it is simple starting method to
With Regards,
Maayan
mdmackillop
04-12-2010, 02:01 PM
... and having recorded it, then try to strip out the verbiage. You can then add the other ranges in the same manner
Sub CopyTo()
Sheets("Sheet1").Range("A1:A100").Copy
Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub
genracela
04-12-2010, 04:21 PM
Great! I'll start the program rightaway!:yes
Thanks!
genracela
04-12-2010, 05:03 PM
quick question... what if data would come from a different workbook not just sheet?
workbook("Sheet1").Select ?
mdmackillop
04-13-2010, 12:32 AM
No need to select. If the book is open then
Sub CopyTo()
Workbooks("Test.xls").Sheets("Sheet1").Range("A1:A100").Copy
Thisworkbook.Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub
MaayanSV
04-13-2010, 12:37 AM
Keep both the workbook open... and run the following macro
Sub CopyTo()
Workbooks("From").Sheets("Sheet1").Range("A1:A100").Copy
Workbooks("To").Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub
genracela
04-13-2010, 12:40 AM
Thanks!:cloud9:
genracela
04-13-2010, 05:29 PM
Another question, what if I want to start pasting on the blank row?
mdmackillop
04-14-2010, 01:08 AM
the blank row?
This doesn't mean anything.
You can change the target to the ActiveCell or otherwise identify using Cells(Rows.Count,1).End(xlUp)(2) or similar.
genracela
04-14-2010, 04:11 PM
yes, i got it. :yes
I added
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
thanks!!! :thumb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.