Outline ·
[ Standard ] ·
Linear+
Excel VBA Copy seleted sheet Value in MasterSheet
TSKahHoong
|
Jul 31 2015, 07:27 PM, updated 9y ago
|
Getting Started
|
Hi there, im a total newbie here, would like to ask a VBA question. I have lets say 7 sheets : sheet1,sheet2,sheet3,sheet4,sheet5,sheet6,Combined. sheet1,sheet2,sheet3,sheet4 have same header category, same field, now i would like to combine them into "Combined" sheet, and only copy and paste them as Values(because those sheet all contain formula and it wasnt required). I was trying around googleing and im stuck. I have a code below, which i think i need to put PasteSpecial value somewhere. The combine sheet already have a header, it will start on A2 row, and every sheet will be pasting values on the last row of the other one. Thanks! CODE
Option Explicit
Sub Collate_Sheets()
Dim wks As Worksheet Set wks = Sheets(Sheets.Count)
Worksheets("Combined").Activate
With Sheets("sheet1_filter") Dim lastrow As Long
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With With Sheets("Sheet2_filter")
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With With Sheets("sheet3_filter")
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With End Sub
|
|
|
|
heliosi
|
Jul 31 2015, 10:06 PM
|
Getting Started
|
Hi, I had a look but can't figure out how to include PasteSpecial into your one-line code. As a quick solution you can just copy the range in Sheets("Combined") and pasted it as values. There should be some VBA pros lurking about here that could help. CODE
Option Explicit
' I put an ActiveX command button on the "Combined" sheet. Private Sub CommandButton1_Click()
Dim wks As Worksheet Dim lastrow As Long Dim i As Integer
Set wks = Sheets(Sheets.Count)
Worksheets("Combined").Activate
' loops through all sheets except last sheet (the "Combined" sheet) For i = 1 To (Sheets.Count - 1) With Sheets(i) lastrow = .Range("B" & .Rows.Count).End(xlUp).Row .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1) End With Next i ' copy the entire range of pasted values, and pastes as err..values With Sheets("Combined") lastrow = .Range("B" & .Rows.Count).End(xlUp).Row .Range("A3:K" & lastrow).Copy .Range("A3:K" & lastrow).PasteSpecial xlPasteValues Application.CutCopyMode = False End With End Sub
[/quote]
|
|
|
|
SincerePrayer
|
Jul 31 2015, 10:48 PM
|
love to pray
|
Hi KahHoong, Everyone was newbie once. Personally, I welcome you to the magical world of VBA. The question of you is fairly simple, if you know the strategies. Do you believe you can use Macro Recording to generate the code below, without even typing a single line of code? (except the comments) Feel free to check out the attached file. I wanted to share all these strategies (and many more tricks) in Malaysia Excel Mastery gathering planned next month. This is social gathering we talk solely on using VBA and Macro to do magic in Excel. Let me know if you and your friends are interested to be part of this upcoming event. Alternatively, I could come to your company for sharing (if you have about 5 person or more). Again, hope this helps. » Click to show Spoiler - click again to hide... « CODE Option Explicit
Sub Collate_Sheets()
' select combine worksheet & its first cell Sheets("Combine").Select Range("A1").Select ' go to first sheet, select all table (include header row) & copy Sheets("Sheet1").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy ' go back to combine worksheet & paste as value Sheets("Combine").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' move to last empty row of the combine worksheet Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ' go to second sheet, select from 2s row (exclude first header row) & copy Sheets("Sheet2").Select Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy ' go back to combine worksheet & paste as value Sheets("Combine").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' move to last empty row of the combine worksheet Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ' go to third sheet, select from 2nd row (exclude first header row) & copy Sheets("Sheet3").Select Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy ' go back to combine worksheet & paste as value Sheets("Combine").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' autofit all columns & return focus back to cell A1 Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select End Sub
Attached File(s)
LYN_Question.zip ( 17.27k )
Number of downloads: 3
|
|
|
|
TSKahHoong
|
Aug 1 2015, 10:46 AM
|
Getting Started
|
QUOTE(SincerePrayer @ Jul 31 2015, 10:48 PM) Hi KahHoong, Everyone was newbie once. Personally, I welcome you to the magical world of VBA. The question of you is fairly simple, if you know the strategies. Do you believe you can use Macro Recording to generate the code below, without even typing a single line of code? (except the comments) Feel free to check out the attached file. I wanted to share all these strategies (and many more tricks) in Malaysia Excel Mastery gathering planned next month. This is social gathering we talk solely on using VBA and Macro to do magic in Excel. Let me know if you and your friends are interested to be part of this upcoming event. Alternatively, I could come to your company for sharing (if you have about 5 person or more). Again, hope this helps. » Click to show Spoiler - click again to hide... « CODE Option Explicit
Sub Collate_Sheets()
' select combine worksheet & its first cell Sheets("Combine").Select Range("A1").Select ' go to first sheet, select all table (include header row) & copy Sheets("Sheet1").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy ' go back to combine worksheet & paste as value Sheets("Combine").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' move to last empty row of the combine worksheet Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ' go to second sheet, select from 2s row (exclude first header row) & copy Sheets("Sheet2").Select Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy ' go back to combine worksheet & paste as value Sheets("Combine").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' move to last empty row of the combine worksheet Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ' go to third sheet, select from 2nd row (exclude first header row) & copy Sheets("Sheet3").Select Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy ' go back to combine worksheet & paste as value Sheets("Combine").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' autofit all columns & return focus back to cell A1 Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select End Sub
Thanks for the help guys, but this will not do, because from my sheet1-4, they're 10000 rows, all fitted with functions. Here's the new situation, sheet1-4 is the raw data paste from various source, then sheet5 will have functions and using data from sheet 1, sheet6 will have functions and using data from sheet 2, sheet7 will have functions and using data from sheet 3,sheet8 will have functions and using data from sheet 4, (sheet 5-8, 10000 row all fitted with function, but i've use IF function (if data sheet1 is empty hence display nothing). now i have to combine sheet 5-8 to "Combined" sheet. I really need to combine selected sheets with just values. Or is there any easy way out for my condition?
|
|
|
|
SincerePrayer
|
Aug 1 2015, 10:08 PM
|
love to pray
|
Hi KahHoong, Let me rephrase you lengthy question. You want combine all sheets into one, and ignore the blanks. If this is the case, try look at my previous attached file. I use different number of rows in different sheets. Another way, attach you working file (ZIP it) here. And, I will get the solution for you. Note: remove all confidential information. What you want is just solution. Hope this helps. QUOTE(KahHoong @ Aug 1 2015, 10:46 AM) Thanks for the help guys, but this will not do, because from my sheet1-4, they're 10000 rows, all fitted with functions. Here's the new situation, sheet1-4 is the raw data paste from various source, then sheet5 will have functions and using data from sheet 1, sheet6 will have functions and using data from sheet 2, sheet7 will have functions and using data from sheet 3,sheet8 will have functions and using data from sheet 4, (sheet 5-8, 10000 row all fitted with function, but i've use IF function (if data sheet1 is empty hence display nothing). now i have to combine sheet 5-8 to "Combined" sheet. I really need to combine selected sheets with just values. Or is there any easy way out for my condition?
|
|
|
|
kent_lau7
|
Aug 5 2015, 07:26 AM
|
|
QUOTE(KahHoong @ Aug 1 2015, 10:46 AM) Thanks for the help guys, but this will not do, because from my sheet1-4, they're 10000 rows, all fitted with functions. Here's the new situation, sheet1-4 is the raw data paste from various source, then sheet5 will have functions and using data from sheet 1, sheet6 will have functions and using data from sheet 2, sheet7 will have functions and using data from sheet 3,sheet8 will have functions and using data from sheet 4, (sheet 5-8, 10000 row all fitted with function, but i've use IF function (if data sheet1 is empty hence display nothing). now i have to combine sheet 5-8 to "Combined" sheet. I really need to combine selected sheets with just values. Or is there any easy way out for my condition? You can watch my explanation here and download your solution here: http://www.ugrowit.net/excel-vba-paste-all...paste-as-value/CODE Sub Collate_Sheets()
' Coded by: Kent Lau, www.ugrowit.net Dim wks As Worksheet Dim lastrow As Long ' Set wks = Sheets(Sheets.Count) Set wks = Worksheets("Combined") ' Worksheets("Combined").Activate lastrow = _ Sheets("Branch01").Range("B" & Rows.Count).End(xlUp).Row ' Copy Branch01 data to Combine Sheets("Branch01").Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1).PasteSpecial _ Paste:=xlPasteValues 'xlPasteAll With Sheets("Branch02") ' Error code: lastrow = .Range("B" & .Rows.Count).End(xlUp).Row lastrow = .Range("B" & Rows.Count).End(xlUp).Row .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1).PasteSpecial _ Paste:=xlPasteValues End With With Sheets("Branch03") lastrow = .Range("B" & Rows.Count).End(xlUp).Row .Range("A2:K" & lastrow).Copy _ wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1) End With End Sub
|
|
|
|