PDA

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