PDA

View Full Version : Macro to automatically paste the selected column into another sheet



Master_Viper
07-17-2016, 02:46 PM
Hello Expert,

I want to ask help. I have workbook in that workbook I have two sheets (Sheets 1 and 2). In sheet 1 I have button that when I clicked it the value/data from sheet 1 will automatically pasted in sheet 2. Please see attach file.

jolivanes
07-17-2016, 10:20 PM
Where do you find the data that you have in Sheet2, Column G, Variant (M)?

jolivanes
07-17-2016, 10:40 PM
Change the "G7" in "sh1.Range("G7").Value" to the cell where you find the value for "Variant".

Sub Maybe()
Dim sh1 As Worksheet, a
Set sh1 = Sheets("Sheet1")
a = Array(sh1.Range("G15").Value, sh1.Range("G5").Value, sh1.Range("G3").Value, sh1.Range("S23").Value, sh1.Range("G7").Value, sh1.Range("N23").Value)
Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1).Resize(, 6).Value = a
End Sub

Master_Viper
07-18-2016, 07:44 AM
Hello Jolivanes, thanks for the code. However, instead of the data will inserted to up position I want it to below the last data.
Thanks again.

jolivanes
07-18-2016, 08:54 AM
I want it to below the last data
That's what it does. It takes Column C as a reference for the "last data"
Have you tried it on a copy of your workbook?
Otherwise attach your workbook so we can have a look at it

Master_Viper
07-18-2016, 09:50 AM
Perfect Jolivanes, Thanks to you.

One more question I add Item and Date in sheet 2. Now my question there's no Item and Date in the sheet 1 what I want to do is when I click the button in the sheet 1 there will be data of item and date automatically in sheet 2. Meaning in item column it will display how many data already in there, while for the date column it will display automatically when i edit the data from sheet 1. Please see attached file for your reference.

Thanks in advanced!

jolivanes
07-18-2016, 10:40 AM
Is this what you have in mind?

Sub Maybe()
Dim sh1 As Worksheet, a
Set sh1 = Sheets("Sheet1")
a = Array(sh1.Range("G15").Value, sh1.Range("G5").Value, sh1.Range("G3").Value, sh1.Range("S23").Value, sh1.Range("G7").Value, sh1.Range("N23").Value)
With Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1)
.Resize(, 6).Value = a
.Offset(, -2).Value = .Row() - 2
.Offset(, -1).Value = Format(Now(), "mmmm-dd-yyyy")
End With
End Sub

Master_Viper
07-18-2016, 11:15 AM
Your great Jolivanes, the code is working when I press the Run Sub in the VBA panel but when I closed the VBA panel and press the update button in the sheet 1 nothing happen or the data from sheet is not updating. Please help me again.

jolivanes
07-18-2016, 11:20 AM
Have a read here. If any more problems, let us know
https://support.office.com/en-us/article/Add-a-button-and-assign-a-macro-to-it-in-a-worksheet-d58edd7d-cb04-4964-bead-9c72c843a283

Master_Viper
07-18-2016, 11:24 AM
Hello Jolivanes,

Now working, it'smy fault I didn't assign macro of the button.

Master_Viper
07-18-2016, 02:14 PM
Hello Jolivanes,

Another question I hope help me again. In sheet 1 I have additional data that are Housing and Housing ID. In the housing number there is only certain number that I would like to copy which is the 1424101 this number would be pasted in the sheet 2 how to do that.

jolivanes
07-18-2016, 03:39 PM
@Master_Viper
I have to leave here right away so if no one else has a solution for you by tomorrow afternoon, I'll have a look at that time.

jolivanes
07-18-2016, 10:51 PM
Got some unexpected time.
In your attached file you have the whole "Housing" number in Column I and that same number with the exception of the first 4 digits in Column J (Housing ID).
If that is not what you want, you have to use the 2nd code here.
This is the code if you want the "Housing" number in Column I included:

Sub Maybe()
Dim sh1 As Worksheet, a
Set sh1 = Sheets("Sheet1")
a = Array(sh1.Range("G15").Value, sh1.Range("G5").Value, sh1.Range("G3").Value, sh1.Range("S23").Value, sh1.Range("G7").Value, _
sh1.Range("N23").Value, sh1.Range("E23").Value, Mid(sh1.Range("E23"), 5, 99))
With Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1)
.Resize(, 8).Value = a
.Offset(, -2).Value = .Row() - 2
.Offset(, -1).Value = Format(Now(), "mmmm-dd-yyyy")
End With
End Sub
This is the code if you do not want the "Housing" number included:

Sub Maybe()
Dim sh1 As Worksheet, a
Set sh1 = Sheets("Sheet1")
a = Array(sh1.Range("G15").Value, sh1.Range("G5").Value, sh1.Range("G3").Value, sh1.Range("S23").Value, sh1.Range("G7").Value, _
sh1.Range("N23").Value, Mid(sh1.Range("E23"), 5, 99))
With Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1)
.Resize(, 7).Value = a
.Offset(, -2).Value = .Row() - 2
.Offset(, -1).Value = Format(Now(), "mmmm-dd-yyyy")
End With
End Sub

Master_Viper
07-19-2016, 07:55 AM
Hello Jolivanes,

The first code above is OK, however I dont want to include the last two zero's like what I shown in the sheet 1 of the attched file.

Master_Viper
07-19-2016, 08:43 AM
Hello Jolivanes,

Problem solved, I just changed th string length from 99 to 7.

Master_Viper
07-19-2016, 08:54 AM
Jolivanes,

I want to add another value after the code below.

Mid(sh1.Range("E23"), 5, 99)), sh1.Range("S444").Value

But I got an error of Compile error: Expected: end of statement.

jolivanes
07-19-2016, 09:49 AM
Change

Mid(sh1.Range("E23"), 5, 99)), sh1.Range("S444").Value
to

Mid(sh1.Range("E23"), 5, 99), sh1.Range("S444").Value)
and

.Resize(, 7).Value = a
to

.Resize(, 8).Value = a

jolivanes
07-19-2016, 10:57 AM
Re: Problem solved, I just changed the string length from 99 to 7.
What if the last two digits are not zeros?

Master_Viper
07-19-2016, 11:17 AM
Thanks Jolivanes your great. I have additional condition.

Let's say at column N24 if the data are N, ONT and PON the condition should be equal to 3Spring if Nest3 the condition should be equal to 4Spring. And the 3Spring or 4Spring data should copied automatically at Sheet 2 under the header Position.
How to do this condition using macro?

Thanks again in advanced!

Master_Viper
07-19-2016, 11:19 AM
I don't really need the last two digits.

jolivanes
07-19-2016, 11:37 AM
N24 is not a Column.
N is a Column and 24 is a Row
N24 is a cell (address)
What is "ONT" & "PON" & "3Spring" & "Nest3" & "4Spring"?
Can't find any of that in your attachment.

Master_Viper
07-19-2016, 11:52 AM
Column N and Row 26 I should say.

If letter "N", "ONT" and "PON" will display in cell address N26 the condition should be 3Spring.

4Spring and 3Spring are not in the attached file, however, that are my conditional formatting.

Master_Viper
07-19-2016, 11:54 AM
I forgot to attach the file.

jolivanes
07-19-2016, 01:23 PM
If you have any more changes and/or additions, start a new thread. Too many changes and additions in this thread.

Sub Maybe()
Dim sh1 As Worksheet, a, b As String
Set sh1 = Sheets("Sheet1")
If sh1.Range("N26") = "N" Or sh1.Range("N26") = "ONT" Or sh1.Range("N26") = "PON" Then b = "3Spring"
a = Array(sh1.Range("G15").Value, sh1.Range("G5").Value, sh1.Range("G3").Value, sh1.Range("S23").Value, sh1.Range("G7").Value, _
sh1.Range("N23").Value, sh1.Range("E23").Value, Mid(sh1.Range("E23"), 5, 99), b)
With Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1)
.Resize(, 9).Value = a
.Offset(, -2).Value = .Row() - 2
.Offset(, -1).Value = Format(Now(), "mmmm-dd-yyyy")
End With
End Sub