VBA USERFORM SCREEN SIZE
I tried to fit the vba userform with laptop screen size.But it is not coming properly.Kindly Suggest
See also questions close to this topic
Excel macro to copy data matching column header between two OPEN WorkBooks
I am looking for a macro to copy data matching column headers between two OPEN workbooks. I have below code to copy data between sheets in the same workbook. But I need as below to copy between TWO OPEN workbooks.
- First open the Destination Workbook (Have a macro button in it to copy data)
- Second open source workbook (to physically see and verify data)
- Third, go to destination workbook and click button to copy.
Can anyone please help me.
Sub CopyMatchingHeaders() Dim wbSource As Workbook Dim SFileName As Variant SFileName = Application.GetOpenFilename("Excel Files, *.xlsx, *.xls*,", MultiSelect:=False) If TypeName(SFileName) = "String" Then Set wbSource = Workbooks.Open(SFileName) Else MsgBox "No file selected." Exit Sub End If Dim header As Range, headers As Range Set headers = ActiveWorkbook.Worksheets("Sheet1").Range("A1:AE1") For Each header In headers If GetHeaderColumn(header.Value) > 0 Then Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=ActiveWorkbook.Worksheets("Sheet2").Cells(2, GetHeaderColumn(header.Value)) End If Next End Sub Function GetHeaderColumn(header As String) As Integer Dim headers As Range Set headers = ActiveWorkbook.Worksheets("Sheet2").Range("A1:AE1") GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0) End Function
keep the rows and column hidden while changing row width and column height
I have a code that change the rows height and column width of all the sheets in the workbook from row 1 and column B. My problem is that its making all my hidden columns and rows also visible. Please suggest as to how I shud modify the code so that it can change the column width and row height but should be kept them hidden.
Sub rowcolallsheetb() Dim exworkb As Workbook Dim xlwksht As Worksheet Dim lastrow1 As Long Dim lastcolumn1 As Long Dim firstrowDB As Long Dim Z As Integer Dim ShtNames() As String ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count) For Z = 1 To Sheets.Count ShtNames(Z) = Sheets(Z).Name Sheets(Z).Select lastrow1 = Sheets(Z).Cells(Rows.Count, "A").End(xlUp).Row lastcolumn1 = Sheets(Z).Cells(1, Columns.Count).End(xlToLeft).Column ActiveWorkbook.Sheets(Z).Range(Sheets(Z).Cells(1, 2), Sheets(Z).Cells(lastrow1, lastcolumn1)).Select Selection.Cells.RowHeight = 9.4 Selection.Cells.ColumnWidth = 11.2 Next Z End Sub
Macro doesnt appear on the List after copy-pasting code
I am completely new to VBA and hence have no idea what I'm doing... Below is the description of my problem and also the code in question.
What I originally wanted to do was this: Copy a clicked cell, Select new sheet, Select any cell, Paste as values
Now i found a code that apparently does the trick which is this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns("A")) Is Nothing Then Cancel = True If Target.Row > 1 And Len(Target.Value) Then Worksheets("S11").Range("C2").Value = Target.Value End If End Sub
I say "apparently" as I am not able to test it. Here is the problem:
- I saved the workbook as excel macro-enabled workbook
- Clicked ALT Q - to go back to my workbook
- I then ALT F8 to run it - but there is nothing there... blank....
what am I missing?